Limit the levels for a query with CONNECT BY

[Bernard Drolet]

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

[Daniel Roy]

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

[sybrandb@yahoo.com]

Re: Limit the levels for a query with CONNECT BY

le_pul@yahoo.ca (Bernard Drolet) wrote in message news:<ee7856eb. 0311041323.7cd8 3304@posting.go ogle.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

[Bernard Drolet]

Re: Limit the levels for a query with CONNECT BY

sybrandb@yahoo. com wrote in message news:<a1d154f4. 0311050031.2f4d 988c@posting.go ogle.com>...

>
Hint: there is a LEVEL pseudo function.
>
Sybrand Bakker
Senior Oracle DBA

I have two concerns with LEVEL:

First,

I do not know in advance at what level the specific row is ! The table
contains the structure and content of complex SGML documents; the SGML
structure (DTD) can be different from one document to the other and
can even change over time

(for example, I can have a document with
PUBLICATION - CHAPTER - SECTION - TASK
structure today, but in future, the person in charge of the
publication can decide to add a layer, as in
PUBLICATION - CHAPTER - SECTION - PAGEBLOCK - TASK,
so the level to find the TASK is now changed and the query will not
find it anymore).

Second,

Even if I ask to display only the, for example, 3 first LEVELs, am I
right when I say the query will process the complete tree structure
anyway before returning me only the rows I'm interested in ?



Bernard Drolet

[Daniel Roy]

Re: Limit the levels for a query with CONNECT BY

If you don't know beforehand the LEVEL value, I'm afraid you will need
first to run a query to find this value. As for your concern that
Oracle will need to process the whole tree just to display N levels,
you're probably right. Run the explain plan, and we'll be able to see
for sure.

Daniel

>
I have two concerns with LEVEL:
>
First,
>
I do not know in advance at what level the specific row is ! The table
contains the structure and content of complex SGML documents; the SGML
structure (DTD) can be different from one document to the other and
can even change over time
>
(for example, I can have a document with
PUBLICATION - CHAPTER - SECTION - TASK
structure today, but in future, the person in charge of the
publication can decide to add a layer, as in
PUBLICATION - CHAPTER - SECTION - PAGEBLOCK - TASK,
so the level to find the TASK is now changed and the query will not
find it anymore).
>
Second,
>
Even if I ask to display only the, for example, 3 first LEVELs, am I
right when I say the query will process the complete tree structure
anyway before returning me only the rows I'm interested in ?
>
>
>
Bernard Drolet

[Alex Filonov]

Re: Limit the levels for a query with CONNECT BY

le_pul@yahoo.ca (Bernard Drolet) wrote in message news:<ee7856eb. 0311050650.4cbb 0e2a@posting.go ogle.com>...

sybrandb@yahoo. com wrote in message news:<a1d154f4. 0311050031.2f4d 988c@posting.go ogle.com>...

Hint: there is a LEVEL pseudo function.

Sybrand Bakker
Senior Oracle DBA

>
I have two concerns with LEVEL:
>
First,
>
I do not know in advance at what level the specific row is ! The table
contains the structure and content of complex SGML documents; the SGML
structure (DTD) can be different from one document to the other and
can even change over time
>
(for example, I can have a document with
PUBLICATION - CHAPTER - SECTION - TASK
structure today, but in future, the person in charge of the
publication can decide to add a layer, as in
PUBLICATION - CHAPTER - SECTION - PAGEBLOCK - TASK,
so the level to find the TASK is now changed and the query will not
find it anymore).
>
Second,
>
Even if I ask to display only the, for example, 3 first LEVELs, am I
right when I say the query will process the complete tree structure
anyway before returning me only the rows I'm interested in ?
>
>

Let's try to reverse the task. If name column is indexed, you can
use something like this:

select id, name
from Mytable a
where name = 'XYZ'
and exists (select 1
from Mytable b
where id = &1
connect by id = prior parent_id
start with name = 'XYZ' and b.id = a.id)

Subquery should represent reverse tree starting from current line of
the main query.

>
Bernard Drolet

[VC]

Re: Limit the levels for a query with CONNECT BY

Hello Bernard,

If I understand correctly what you want to do, then it's really simple:

SELECT id, name
FROM Mytable
WHERE name = 'XYZ'
STARTING WITH id = &1
CONNECT BY id = PRIOR id AND PRIOR name < 'XYZ';


Rgds.


"Bernard Drolet" <le_pul@yahoo.c awrote in message
news:ee7856eb.0 311050650.4cbb0 e2a@posting.goo gle.com...

sybrandb@yahoo. com wrote in message

news:<a1d154f4. 0311050031.2f4d 988c@posting.go ogle.com>...

Hint: there is a LEVEL pseudo function.

Sybrand Bakker
Senior Oracle DBA

>
I have two concerns with LEVEL:
>
First,
>
I do not know in advance at what level the specific row is ! The table
contains the structure and content of complex SGML documents; the SGML
structure (DTD) can be different from one document to the other and
can even change over time
>
(for example, I can have a document with
PUBLICATION - CHAPTER - SECTION - TASK
structure today, but in future, the person in charge of the
publication can decide to add a layer, as in
PUBLICATION - CHAPTER - SECTION - PAGEBLOCK - TASK,
so the level to find the TASK is now changed and the query will not
find it anymore).
>
Second,
>
Even if I ask to display only the, for example, 3 first LEVELs, am I
right when I say the query will process the complete tree structure
anyway before returning me only the rows I'm interested in ?
>
>
>
Bernard Drolet

[Bernard Drolet]

Re: Limit the levels for a query with CONNECT BY

I found my solution. To stop connecting after I found my required
levels of rows, I added the following condition in my query (I'm
looking down to a row named XYZ, and I do not want to go further):

SELECT
id
FROM
MyTable
WHERE
name = 'XYZ'
START WITH id = 1
CONNECT BY parentid = PRIOR id AND
PRIOR name != 'XYZ' -- THIS SOLVES THE PROBLEM !


So it will never accept to go further than my level with name 'XYZ'

I must admit I was not aware the CONNECT BY could be used with a
condition more complex than just the tree structure. I shall read
better the documentation next time.


Thank you all


Bernard Drolet