Home » SQL & PL/SQL » SQL & PL/SQL » Foreign Key Dependency Query
Foreign Key Dependency Query [message #321603] Tue, 20 May 2008 17:40 Go to next message
Peewee1476
Messages: 5
Registered: May 2008
Junior Member
Hello all. I'm working on a query that needs to get foreign key dependencies on one or more tables at a time. For the results I need to have the name of the table that is being queried for foreign keys, the name of the column that the foreign key exists on, the name of the parent table for the foreign key and the name of the column of the parent table with the foreign key dependency (same result as the child column, but this result is placed into a column with a different name so that the data can be acquired later).

That may be a little confusing so here's the statement I am currently using:

SELECT A.CONSTRAINT_NAME, A.TABLE_NAME, B.COLUMN_NAME, B.COLUMN_NAME AS REFERENCED_COLUMN_NAME, B.TABLE_NAME AS REFERENCED_TABLE_NAME FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.CONSTRAINT_TYPE = 'R' AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME AND (A.TABLE_NAME = @TABLE_NAME0)

This is working ok as long as I am only getting the FKs for one table, as in the above example. For instance, one of the tables I'm using is named SET_AGENT_LOOKUP_VALS. If I use that in place of @TABLE_NAME0 I receive the following:

http://img140.imageshack.us/img140/4788/oraclequeryuv2.jpg
Shot at 2008-05-20


So everything seems nice. But when I add more tables to the query, making the statement for example:

SELECT A.CONSTRAINT_NAME, A.TABLE_NAME, B.COLUMN_NAME, B.COLUMN_NAME AS REFERENCED_COLUMN_NAME, B.TABLE_NAME AS REFERENCED_TABLE_NAME FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.CONSTRAINT_TYPE = 'R' AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME AND (A.TABLE_NAME = 'SET_AGENT_LOOKUP_VALS' OR A.TABLE_NAME = 'SET_AGENTTYPES')


The query still returns the foreign keys, but it takes FOREVER to execute. I know this is somehow a badly formed statement. I guess it's referencing back on itself a whole bunch of times or something like that. I'm not very experienced with Oracle so I don't know exactly how to correct this to reform the statement and still be able to get results for multiple tables. Any help is appreciated. Thanks!
Re: Foreign Key Dependency Query [message #321619 is a reply to message #321603] Tue, 20 May 2008 21:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT A.CONSTRAINT_NAME, A.TABLE_NAME, B.COLUMN_NAME,
       B.COLUMN_NAME AS REFERENCED_COLUMN_NAME, 
       B.TABLE_NAME AS REFERENCED_TABLE_NAME 
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B 
WHERE A.CONSTRAINT_TYPE = 'R' AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
/

204 rows selected.


above ran for whole schema in under 5 seconds.

[Updated on: Tue, 20 May 2008 21:02] by Moderator

Report message to a moderator

Re: Foreign Key Dependency Query [message #321621 is a reply to message #321603] Tue, 20 May 2008 21:07 Go to previous messageGo to next message
Peewee1476
Messages: 5
Registered: May 2008
Junior Member
Hm, I just tried that and it took 30-40 seconds for it to complete. Perhaps I'm using a database with more tables than you? There's a few hundred in here. Any way to make it faster? (Not that I'm ungrateful)
Re: Foreign Key Dependency Query [message #321622 is a reply to message #321603] Tue, 20 May 2008 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Drop down to the Performance Tuning sub-forum & read the STICKY post at the top which contains tuning suggestions.

Why is 45 seconds unacceptable?
Re: Foreign Key Dependency Query [message #321626 is a reply to message #321603] Tue, 20 May 2008 21:29 Go to previous messageGo to next message
Peewee1476
Messages: 5
Registered: May 2008
Junior Member
Mostly because we're running a program that supports both Oracle and SQL Server and the SQL Server equivalent query takes < 1 second to run:

SELECT OBJECT_NAME(FKEYID) AS TABLE_NAME,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME, COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME FROM SYSFOREIGNKEYS WHERE (OBJECT_NAME(RKEYID)='SET_STRAT_MTXNAMES' OR OBJECT_NAME(RKEYID)='SET_STRAT_MTX' OR OBJECT_NAME(RKEYID)='SET_STRAT_SUBCATS' OR OBJECT_NAME(RKEYID)='SET_STRAT_MTX')


So there's a pretty big difference between the two. If that's the expected time for the query to run in Oracle then I guess there's nothing that can be done about it. I don't fully understand why there's such a huge discrepancy but I am a novice Oracle user.
Re: Foreign Key Dependency Query [message #321924 is a reply to message #321626] Wed, 21 May 2008 17:55 Go to previous messageGo to next message
Peewee1476
Messages: 5
Registered: May 2008
Junior Member
So....does anyone know a way to make this faster?
Re: Foreign Key Dependency Query [message #321933 is a reply to message #321603] Wed, 21 May 2008 19:51 Go to previous messageGo to next message
Peewee1476
Messages: 5
Registered: May 2008
Junior Member
Also, if someone could explain to me why adding the 'OR A.TABLE_NAME = TABLE_NAME0' onto the end of the statement makes it take exponentially longer than if it's just looking for one table I'd be able to understand a little better what's going on.
Re: Foreign Key Dependency Query [message #321934 is a reply to message #321603] Wed, 21 May 2008 19:55 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Drop down to the Performance Tuning sub-forum & read the STICKY post at the top which contains tuning suggestions.

[Updated on: Wed, 21 May 2008 20:02] by Moderator

Report message to a moderator

Previous Topic: help needed in simple pl/sql procedure
Next Topic: alternative for mutating error
Goto Forum:
  


Current Time: Sun Dec 04 02:32:34 CST 2016

Total time taken to generate the page: 0.07759 seconds