如何在 Oracle 12c 中調整對 all_constraints 的查詢？

如何調整以下查詢，因為它大約需要 8 秒：

select constraint_name,table_name
               from  all_constraints 
        where  r_constraint_name in
                              (select  constraint_name
                                 from  all_constraints
                               where table_name='SUPPLIER');

SUPPLIER是一個範例表名。

查詢字典視圖時，規則提示通常會有所幫助。

select /*+ RULE */ constraint_name,table_name
               from  all_constraints 
        where  r_constraint_name in
                              (select  constraint_name
                                 from  all_constraints
                               where table_name='SUPPLIER');

但是您的查詢不准確。對像也有所有者。

select /*+ RULE */ owner, constraint_name,table_name
               from  all_constraints 
        where  (r_owner, r_constraint_name) in
                              (select  owner,constraint_name
                                 from  all_constraints
                               where table_name='SUPPLIER'
                                     and owner='SOMEONE');

但是這個查詢可以表述為一個連接。

select /*+ RULE +*/ ref.owner, ref.constraint_name,ref.table_name
 from  all_constraints ref, all_constraints cons
 where ref.r_constraint_name=cons.constraint_name
   and ref.r_owner=cons.owner
   and cons.owner='&OWNER'
   and cons.table_name='&TABLE';

如果您更喜歡現代的 JOIN 語法

select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
 from  all_constraints ref JOIN all_constraints cons 
   on (ref.r_owner=cons.owner and ref.r_constraint_name=cons.constraint_name)
 where 
 cons.owner='&OWNER'
 and cons.table_name='&TABLE';

這是一些計時結果，在 12c 數據庫 (12.1.0.2.0) 上有和沒有 RULE 提示

SQL> alter system flush shared_pool;     

System altered.

Elapsed: 00:00:00.44
SQL> select ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:03.95
SQL> select ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.00
SQL> alter system flush shared_pool;   

System altered.

Elapsed: 00:00:00.02
SQL> select ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:03.94
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.27
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.16

我還使用收集了有關 SYS 對象的統計資訊

SQL> exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', -
> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
> method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:03:41.58

但這沒有幫助。

在 11g 系統 (11.2.0.4.0) 上，沒有提示的查詢性能要好得多，但即使有提示也可以加速查詢

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select  ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.65
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select  ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.66
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL> select /*+ RULE */ ref.owner, ref.constraint_name,ref.table_name
 2    from  all_constraints ref, all_constraints cons
 3    where ref.r_constraint_name=cons.constraint_name
 4    and ref.r_owner=cons.owner
 5    and cons.owner='OWNER'
 6    and cons.table_name='TABLE';

no rows selected

Elapsed: 00:00:00.18
SQL> 

引用自：https://dba.stackexchange.com/questions/114631