Re: Help Finding Circular Reference

"Artful Dodger" <dodger.takethisout_at_answerdude.net> wrote in message news:<bo75ja$9t4$1_at_news.storm.ca>...
> Hi Foks, I wonder of one of the experts here could help me with a problem...
>
> I have a table, with a parent-child relationship established between some
> records, using the following fields:
>
> ID_CODE , SERIAL_NO, PARENT_ID_CODE, PARENT_SERIAL.
>
> The "PARENT_ xxx" fields on each child record contain the ID_CODE and SERIAL
> of the parent record in the same table. Each parent can have multiple child
> records, and each child record can in turn, be a parent to other child
> records, and so on.
>
> When I run a particular report against this table, the report locks up. The
> report query contains a Connect By:
>
> CONNECT BY PRIOR PARENT_ID_CODE = ID_CODE
> AND PRIOR PARENT_SERIAL = SERIAL
> START WITH ID_CODE = A AND SERIAL_NO = 1;
>
> to ensure the entire chain of records is retrieved starting with the
> top-level parent record, on down through all the children, sub-children,
> etc. It appears that the CONNECT BY clause is going into an infinite loop as
> a result of a circular reference somewhere in the table. Example:
>
> ID_CODE SERIAL_NO PARENT_ID_CODE PARENT_SERIAL
> ---------- -------------- --------------------- -----------
> ----------
> A 1 C
> 3
> B 2 A
> 1
> C 3 B
> 2
>
> I've written a simple query which finds any circular reference between the
> 1st and 2nd level, but I'm having a difficult time coming up with a query
> which will identify the offenders if there are more than two levels in the
> relationship. I don't need to correct the report query, I simply need a way
> to identify any chain of records with a circular reference.
>
> Any help with this would be VERY much appreciated.
>
> If at all possible, a CC of your reply to "dodger at answerdude dot net"
> (replace at & dot) would put me forever in your debt!
>
> Thanks in advance,
>
> Dodger

Dodger, Oracle has logic to detect infinite recursion in the connect by cause so if that error is not being returned then this may just be a poorly performing query.

Have you looked at the explain plan to see how it is being solved. I would expect that there should be an index on "id_code, serial_no" and on "parent_id, parent_serial_no" and that these two indexes should be in use. Also I would hope that serial and serial_no are actually one column otherwise make sure you are connecting on the correct columns.

HTH -- Mark D Powell -- Received on Tue Nov 04 2003 - 09:55:25 CST