Cyclic Dependency
994298Mar 1 2013 — edited Mar 2 2013It's been a while since I worked with SQL Plus . I am using Oracle 11g. We are working on a legacy data migration project. I have a table of records with circular dependency records. i am trying to identify the records. I have the foll. columns- Product,Source,target. I want o identify the records which form a loop. For eg.
Source Target
A B
B C
C D
D A
Last record forms a loop-I need to identify these records. My query is below-
SELECT DISTINCT SOURCE,TARGET FROM RULESELIB WHERE CONNECT_BY_ISCYCLE=1 CONNECT BY NOCYCLE SOURCE=PRIOR TARGET;
I ran this query on 2 tables- one with 75000 records and the other with 25000 records.
It works fine on the table with 75000 records completes within a minute but it does not complete on the other table.
I can't seem to be able to figure out the issue with the query or is there something about the data that is causing this query to loop infinitely?
Thanks in advance!
-VU