6

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.

10

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> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.