How to tune the query on all_constraints in Oracle 12c? How to tune the following query, as it's taking around 8 seconds:

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


SUPPLIER is an example table name.
Often the RULE-hint helps when querying dictionary views.

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


But your query is not precise. Objects have an owner, too.

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');


But this query can be formulated as a join.

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='&amp;OWNER'
    and cons.table_name='&amp;TABLE';


If you prefer the modern JOIN syntax

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='&amp;OWNER'
  and cons.table_name='&amp;TABLE';


here are some timing results, with and without RULE hint on a 12c database (12.1.0.2.0)

SQL&gt; alter system flush shared_pool;     

System altered.

Elapsed: 00:00:00.44
SQL&gt; 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&gt; 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&gt; alter system flush shared_pool;   

System altered.

Elapsed: 00:00:00.02
SQL&gt; 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&gt; alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL&gt; 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&gt; 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


I also gathered statistics  on the SYS objects using

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

PL/SQL procedure successfully completed.

Elapsed: 00:03:41.58


but that didn't help.

On 11g systems (11.2.0.4.0) the query without hint performs far better, but even there the hint accelerates the query

SQL&gt; alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL&gt; 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&gt; alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL&gt; 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&gt; alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
SQL&gt; 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&gt;