Re: Help Finding Circular Reference

From: Artful Dodger <dodger.takethisout_at_answerdude.net>
Date: Tue, 4 Nov 2003 18:21:21 -0500
Message-ID: <bo9c90$3kr$1@news.storm.ca>

Hi Mark,

Thanks for the reply. Of course, you're absolutely correct. I tried some experimentation & discovered that the problem cannot be being caused by a circular reference, as the CONNECT BY would definately generate an error in this case.

I'm still stumped as to why my report (and the associated query in SQL Plus) is locking up, as it runs quite nicely on 16 other similar databases. I got so tied up in trying to find the circular reference, I sort of lost track of the original problem. :-) Now, back to the original query to tear it down & see if I can find the REAL cause of the problem!

Thanks again!

Dodger

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0311040755.6428fa9b_at_posting.google.com...
> "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 - 17:21:21 CST