mahjag
asked on
understanding hierarchial query in oracle
Hi
I have a with clause query as follows
with first_set
as
(
select order_id, master_order_id
from TableA
where start_date = '01-MAR-2011'
and group_rec_id = '1-A'
),
hierarchy_query as
(
select tableB.order_id,A.order_id , A.master_order_id
from TableB, first_set A
where TableB.flag_value = 'N'
START WITH TableB.start_date = A.start_date
AND (TableB.order_id = A.order_id or TableB.order_id = a.master_order_id)
CONNECT BY PRIOR master_order_id = TableB.order_id
AND PRIOR TableB.start_date= TableB.start_date
The above query works if the first_set is hard coded to test for some records like group_rec_id = '1-A' but if you take away the testing and run for all the group records in the Month of march (start_date = '01-MAR-2011') the query is running forever or hanged
I need to understand how hierarchy query works to understand why this is hapenning - can someone explain what is wrong in my hierarchy query with examples
Thanks!!
I have a with clause query as follows
with first_set
as
(
select order_id, master_order_id
from TableA
where start_date = '01-MAR-2011'
and group_rec_id = '1-A'
),
hierarchy_query as
(
select tableB.order_id,A.order_id
from TableB, first_set A
where TableB.flag_value = 'N'
START WITH TableB.start_date = A.start_date
AND (TableB.order_id = A.order_id or TableB.order_id = a.master_order_id)
CONNECT BY PRIOR master_order_id = TableB.order_id
AND PRIOR TableB.start_date= TableB.start_date
The above query works if the first_set is hard coded to test for some records like group_rec_id = '1-A' but if you take away the testing and run for all the group records in the Month of march (start_date = '01-MAR-2011') the query is running forever or hanged
I need to understand how hierarchy query works to understand why this is hapenning - can someone explain what is wrong in my hierarchy query with examples
Thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would like to mention a reference to an article in EE itself(written by ivostoykov) which would give you a detailed understanding of level heirarchies if you like to know more...
https://www.experts-exchange.com/Database/Oracle/A_2450-Connect-by-prior.html