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 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 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 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
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 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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
| |