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

    Unanswered: How to retrieve the Foreign key information in Oracle

    I want to know how to retrieve the Foreign Key information in Oracle while using SQL Statement?

    I have use three SQL statement to retrieve such information, but the performance is very bad.

    The three SQL Statements are:
    Select constraint_name, r_constraint_name from all_constraints where constraint_type = 'R' and table_name = table1;

    Select column_name from all_cons_columns where constraint_name = cons1;

    Select table_name, column_name from all_cons_columns where constraint_name = r_ccons1;

    Do anyone know another method to retrieve the Foreign Key information which has 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

    When using any ALL% view, you must specify the OWNER of the object e.g.:

    ... and owner = 'SCOTT' ...

    That will improve performance dramatically (and ensure you get the correct result!)

  3. #3
    Join Date
    May 2003
    Posts
    87

    Re: How to retrieve the Foreign key information in Oracle

    Here are 2 scripts -- use whichever you want !!

    Code:
    -- getpktab.sql
    -- script to list all parent tables
    prompt ***Script to find the parent table/column***
    accept tabnm prompt 'Table Name : '
    -- accept colnm prompt 'Column Name : '
    
    COLUMN owner        FORMAT A10
    COLUMN table_name   FORMAT A15
    COLUMN column_name  FORMAT A20
    COLUMN parent_table FORMAT A15
    COLUMN parent_pk    FORMAT A20
    
    set lines 100
    set feed off
    set verify off
    set term on
    set pages 44
    break on table_name
    
    select a.table_name, b.column_name, c.table_name parent_table, d.column_name parent_pk
    from all_constraints a, all_cons_columns b, all_constraints c, all_cons_columns d
    where a.constraint_name = b.constraint_name
      and b.table_name=upper('&&tabnm')
      and a.r_constraint_name is not null
      and a.r_constraint_name=c.constraint_name
      and c.constraint_name=d.constraint_name
    /
    
    undef tabnm
    set feed on
    clear breaks
    
    -- getfktab.sql
    -- script to list all child tables
    prompt ***Script to find the child table/column***
    accept tabnm prompt 'Table Name : '
    
    COLUMN column_name  FORMAT A20
    COLUMN parent_table FORMAT A20
    COLUMN parent_pk    FORMAT A20
    COLUMN child_table  FORMAT A30
    COLUMN child_pk     FORMAT A20
    
    set lines 100
    set feed off
    set verify off
    set term on
    set pages 44
    break on parent_table on column_name
    
    SELECT a.owner||'.'||a.table_name parent_table, b.column_name, c.owner||'.'||c.table_name child_table, d.column_name child_pk 
    FROM all_constraints a, all_cons_columns b, all_constraints c, all_cons_columns d 
    WHERE a.constraint_name = b.constraint_name
      AND a.constraint_name = c.r_constraint_name
      AND c.constraint_name = d.constraint_name
      AND a.table_name = upper('&&tabnm')
    ORDER BY child_table
    /
    
    undef tabnm
    set feed on
    clear breaks
    Originally posted by kennypoon
    I want to know how to retrieve the Foreign Key information in Oracle while using SQL Statement?

    I have use three SQL statement to retrieve such information, but the performance is very bad.

    The three SQL Statements are:
    Select constraint_name, r_constraint_name from all_constraints where constraint_type = 'R' and table_name = table1;

    Select column_name from all_cons_columns where constraint_name = cons1;

    Select table_name, column_name from all_cons_columns where constraint_name = r_ccons1;

    Do anyone know another method to retrieve the Foreign Key information which has better performance?

  4. #4
    Join Date
    Mar 2002
    Posts
    25
    Thank you all.

    Sorry that I have missed type the owner in this post, I have already use it before I post the question.

    And also I have use join views first, then the performance is very poor, so I change to use three separate SQL statements, the performance is better, but still very poor. I have counted use my three SQL statement with owner, then totally use 30 seconds to get the results. It's very slow.

    Why I use all_... views, because some tables that give the privllege to user to select, insert or delete which is not owned by the user. So I can't use user_... views.

Posting Permissions

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