Optimize Hierarchy Query???

[Chao]

Hi,

I would like to find out if there is another way to optimize my query or other ways I can make it run faster. The query attempts to determine the nodes in the List where the node return is the highest level in its branch within the List. The query takes around 5 minutes.

Your feedback is appreciated. Thanks in advance.

i.e. List.Node_ID Nodes.Parent_Node_ID
595 1
596 595
3527 595 ...
673 2
720 700
the result should be {595, 673, 720}.

Table: List Table: Nodes
Fields: Node_ID Fields: Node_ID
Node_Category_ID Parent_Node_ID
Node_Status ...
Index: Node_ID Index: Node_ID
Parent_Node_ID
select list.node_id
from List
where List.node_category_id = 12
and List.node_status = 'A'
and List.Node_id NOT IN (
select distinct node_id
from Nodes
start with parent_node_id IN (
select node_id
from List
where node_Category_ID = 12
And NODE_STATUS = 'A')
CONNECT BY parent_node_ID = node_ID
) ;

 

[BenediktEngels]

evem though i did not understand completely
what you want with your statement
(i could not get your result {595,673,720}
mayby i missinterpreted your data)

seems to be a lot of sequential scans on table list

1a) may be an index on table list,
fields node_category_id, node_status, node_id
may help.
and/or
1b) reorder your where_clause
to get existing index
on table list, field node_id used
from
....yours
where node__category_id = 12
and node_status = 'A'
and node_id not in ...
....to a reordered where_clause
where node_id not in ...
and node__category_id = 12
and node_status = 'A'

2) to analyze, why a sql statement is slow
you may use tkprof to get an Execution Plan
to realy see what the problem is.
it is easy to use
and you find examples in oracle's documantation

Oracle8i Server and SQL*Plus:
....
Oracle8i Designing and Tuning for Performance
...
Chapter: Using SQL Trace and TKPROF
...
....