Re: Limit the levels for a query with CONNECT BY

Use the "level" implicit variable. You can read about it in th PL/SQL manual from Oracle.

Daniel

 > 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
Received on Wed Nov 05 2003 - 05:02:12 CET