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]

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]

le****@yahoo.ca (Bernard Drolet) wrote in message news:<ee**************************@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

[Bernard Drolet]

sy******@yahoo.com wrote in message news:<a1**************************@posting.google. 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]

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]

le****@yahoo.ca (Bernard Drolet) wrote in message news:<ee**************************@posting.google. com>...

sy******@yahoo.com wrote in message news:<a1**************************@posting.google. 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]

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****@yahoo.ca> wrote in message
news:ee**************************@posting.google.c om...

sy******@yahoo.com wrote in message

news:<a1**************************@posting.google. 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]

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