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
) ;
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
) ;