Re: Limit the levels for a query with CONNECT BY

le_pul_at_yahoo.ca (Bernard Drolet) wrote in message news:<ee7856eb.0311041323.7cd83304_at_posting.google.com>...
> Hi, I have a table containing many millions of rows.
> This table has a tree stucture, with the following columns
>
> id
> name
> parent_id
>
> I need to go through the tree, starting from a specific Id, to find
> all the rows with a specific name.
>
> If I use the following:
>
> SELECT id, name
> FROM Mytable
> WHERE name = 'XYZ'
> STARTING WITH id = &1
> CONNECT BY id = PRIOR id;
>
> I will find them, but if will traverse the complete tree structure,
> even below the rows that contains name='XYZ'. The XYZ can be near the
> starting point, let say 2 or 3 levels, when the tree structure can go
> down to 50 levels and more (the table is used to store complex SGML
> documents).
>
> Is there a way, to save processing time, to say to the query : "Do not
> go below XYZ ?" eg when you encounter the 'XYZ' stop connecting below
> and go to another branch ?
>
>
>
>
> Thank you
>
> Bernard Drolet

Hint: there is a LEVEL pseudo function.

Sybrand Bakker
Senior Oracle DBA Received on Wed Nov 05 2003 - 09:31:09 CET