473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Limit the levels for a query with CONNECT BY

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
Jul 19 '05 #1
7 15256
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

Jul 19 '05 #2
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
Jul 19 '05 #3
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
Jul 19 '05 #4
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

Jul 19 '05 #5
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

Jul 19 '05 #6
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

Jul 19 '05 #7
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
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Gary | last post by:
Hi, guys! Some of my applications are sharing same SQL login/password to connect to a database called "MyDB" on server "MyServer" . The password is encrypted and stored in registry or some...
2
by: Dom | last post by:
I need to run a query based on a query in Access. The second query has a number of conditions which all work well, but there is one more contition I need to set to make it run properly. the...
2
by: SJM | last post by:
In some VBA code I am building an SQL string to append a new record to a table. The string involves 40 fields. What I am finding is that it works with 39 fields but when I add 40 it fails with the...
5
by: Shane | last post by:
I wonder if someone has any ideas about the following. I am currently producing some reports for a manufacturing company who work with metal. A finished part can contain multiple sub-parts to...
3
by: deko | last post by:
Is there any way to limit the number of records loaded into a ListBox? I looked at qdf.MaxRecords (to apply to the query that is the RowSource of the ListBox) but that only applies to ODBC data...
4
by: Bill | last post by:
Hi, I would be grateful if someone could clarify my rather confused ideas of the 10 connection limit on XP/2000 when its being used as a server. (I realise that XP is really a client op sys with...
0
by: teju | last post by:
Hi all, I am trying to populate tree view from the database. Till two levels i can populate it fine but when it reaches third level it doesn't expand. Below is the code, it has been taken from the...
7
by: Bernard Drolet | last post by:
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,...
0
by: federico | last post by:
Hello, is there a way to limit the time a "ManagementScope.Connect" execution takes to end before it times out by itself? I tried using ConnectionOptions.TimeOut property to no avail. I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.