Re: Help Finding Circular Reference

"Artful Dodger" <dodger.takethisout_at_answerdude.net> wrote in message news:<bo9c90$3kr$1_at_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 --

I suggest you run the explain plan then update the statistics for the involved table/indexes and rerun the plan to see if anything changed. You might also check the plan against the plan for a db where the query is working well and the data quantity is similar.

HTH -- Mark D Powell -- Received on Wed Nov 05 2003 - 18:52:46 CST