Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    25

    Unanswered: How to retrieve the Foreign key information in Oracle

    I use this SQL statement
    Select pka.table_name PK_Table_Name, pka.column_name PK_COLUMN_NAME, pkc.table_name FK_TABLE_NAME, pkc.column_name FK_COLUMN_NAME from user_cons_columns pka, user_constraints pkb, user_cons_columns pkc where pkc.constraint_name = pkb.constraint_name and pkb.constraint_type='R' and pka.constraint_name = pkb.r_constraint_name and pka.position = pkc.position and pka.table_name = 'ABC' order by pka.table_name, pka.column_name, pkc.table_name, pkc.column_name

    but the performance is too slow. Is any other statement which has much better performance?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to retrieve the Foreign key information in Oracle

    Originally posted by kennypoon
    I use this SQL statement
    Select pka.table_name PK_Table_Name, pka.column_name PK_COLUMN_NAME, pkc.table_name FK_TABLE_NAME, pkc.column_name FK_COLUMN_NAME from user_cons_columns pka, user_constraints pkb, user_cons_columns pkc where pkc.constraint_name = pkb.constraint_name and pkb.constraint_type='R' and pka.constraint_name = pkb.r_constraint_name and pka.position = pkc.position and pka.table_name = 'ABC' order by pka.table_name, pka.column_name, pkc.table_name, pkc.column_name

    but the performance is too slow. Is any other statement which has much better performance?
    It will run quicker if you add these conditions:

    and pka.owner = pkb.owner
    and pkb.owner = pkc.owner

    This is (I believe) because owner is the leading part of the index used to join the tables, and so cannot be used if the conditions are omitted.

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    Can you elimitane two of the ORDER BY conditions? Seems you are ordering the results on similar criteria!!!


    Hope that helps,

    clio_usa - OCP - DBA

    dbaclick.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •