Link to home
Start Free TrialLog in
Avatar of mahjag
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!!
ASKER CERTIFIED SOLUTION
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Author, wpcortes has well explained the scenario..

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