How to tune the query on all_constraints in Oracle 12c?

Question

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.

Answer 1

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

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

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

I also gathered statistics on the SYS objects using

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

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> 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>