understanding hierarchial query in oracle
Posted on 2012-03-17
I have a with clause query as follows
select order_id, master_order_id
where start_date = '01-MAR-2011'
and group_rec_id = '1-A'
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