Query the database;

Hello

As Mr Dan mentioned, you can do move the condition mentioned in 'where' clause to Join statements .Something like below

SELECT NULL AS pktable_cat,

      p.owner as pktable_schem,

      p.table_name as pktable_name,

      pc.column_name as pkcolumn_name,

      NULL as fktable_cat,

      f.owner as fktable_schem,

      f.table_name as fktable_name,

      fc.column_name as fkcolumn_name,

      fc.position as key_seq,

      NULL as update_rule,

      decode (f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1) as delete_rule,

      f.constraint_name as fk_name,

      p.constraint_name as pk_name,

      decode(f.deferrable, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRED', 6 ) deferrability

FROM all_cons_columns pc ,

      INNER JOIN all_constraints p ON

               p.table_name = '?' /*Bind1*/ AND

               p.owner = '?' /*Bind2*/ AND,

               p.constraint_type = 'P' AND,

               pc.owner = p.owner AND,

               pc.constraint_name = p.constraint_name AND,

               pc.table_name = p.table_name

      INNER JOIN all_cons_columns fc ON

               fc.position = pc.position

      INNER JOIN all_constraints f

               f.constraint_type = 'R' AND

               p.owner = f.r_owner AND

               p.constraint_name = f.r_constraint_name AND

               fc.owner = f.owner AND

               fc.constraint_name = f.constraint_name AND

               fc.table_name = f.table_name

WHERE 1 = 1

ORDER BY fktable_schem, fktable_name, key_seq

Advantage is that if you are using the conditions in join statement the query results are filtered on joinning tables itself rather than taking the whole data and gets filtered by where clause. This will improve the query performance and the results will also be correct ones

Hope this help

Regards

Sreekar

To make it run faster you can put your joins in the from clause instead of the where clause.  You can also make it a stored procedure.

If you want to remove it, find out where you are using the results and see if there is another way.