Solved

understanding hierarchial query in oracle

Posted on 2012-03-17
2
281 Views
Last Modified: 2012-03-19
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!!
0
Comment
Question by:mahjag
2 Comments
 
LVL 15

Accepted Solution

by:
Walter Ritzel earned 310 total points
ID: 37733878
The principle of the hierarchy query is to build a tree with the information in a table.
The most classical example is, for example, to build a employee functional hierarchy (like john reports to jack that reports to phill and so on...).
In this example, this is possible because the table employee has the following structure:
create table employees (
employee_id integer,
first_name varchar2(50),
manager_id integer);

Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (100,'Steven',null);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (101,'Neena',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (102,'Lex',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (103,'Alexander',102);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (104,'Bruce',103);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (105,'David',103);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (106,'Valli',103);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (107,'Diana',103);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (108,'Nancy',101);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (109,'Daniel',108);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (110,'John',108);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (111,'Ismael',108);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (112,'Jose Manuel',108);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (113,'Luis',108);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (114,'Den',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (115,'Alexander',114);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (116,'Shelli',114);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (117,'Sigal',114);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (118,'Guy',114);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (119,'Karen',114);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (120,'Matthew',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (121,'Adam',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (122,'Payam',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (123,'Shanta',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (124,'Kevin',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (125,'Julia',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (126,'Irene',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (127,'James',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (128,'Steven',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (129,'Laura',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (130,'Mozhe',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (131,'James',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (132,'TJ',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (133,'Jason',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (134,'Michael',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (135,'Ki',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (136,'Hazel',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (137,'Renske',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (138,'Stephen',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (139,'John',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (140,'Joshua',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (141,'Trenna',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (142,'Curtis',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (143,'Randall',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (144,'Peter',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (145,'John',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (146,'Karen',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (147,'Alberto',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (148,'Gerald',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (149,'Eleni',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (150,'Peter',145);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (151,'David',145);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (152,'Peter',145);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (153,'Christopher',145);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (154,'Nanette',145);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (155,'Oliver',145);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (156,'Janette',146);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (157,'Patrick',146);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (158,'Allan',146);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (159,'Lindsey',146);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (160,'Louise',146);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (161,'Sarath',146);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (162,'Clara',147);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (163,'Danielle',147);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (164,'Mattea',147);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (165,'David',147);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (166,'Sundar',147);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (167,'Amit',147);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (168,'Lisa',148);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (169,'Harrison',148);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (170,'Tayler',148);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (171,'William',148);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (172,'Elizabeth',148);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (173,'Sundita',148);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (174,'Ellen',149);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (175,'Alyssa',149);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (176,'Jonathon',149);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (177,'Jack',149);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (178,'Kimberely',149);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (179,'Charles',149);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (180,'Winston',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (181,'Jean',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (182,'Martha',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (183,'Girard',120);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (184,'Nandita',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (185,'Alexis',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (186,'Julia',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (187,'Anthony',121);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (188,'Kelly',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (189,'Jennifer',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (190,'Timothy',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (191,'Randall',122);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (192,'Sarah',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (193,'Britney',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (194,'Samuel',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (195,'Vance',123);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (196,'Alana',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (197,'Kevin',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (198,'Donald',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (199,'Douglas',124);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (200,'Jennifer',101);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (201,'Michael',100);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (202,'Pat',201);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (203,'Susan',101);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (204,'Hermann',101);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (205,'Shelley',101);
Insert into HR.EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,MANAGER_ID) values (206,'William',205);
commit;

Open in new window


where manager_id is foreign to the table emp itself, meaning that a manager is also an employee.

With that table, it is possible to build the hierarchy from root to leaf or leaf to root, meaning that you can get a leaf on the tree and reach the root or, from the root, list all the tree.

-- this query list all the tree, starting on the root
select lpad(' ',level) || employee_id || '-' || first_name from employees
start with employee_id = 100
connect by manager_id = prior employee_id

-- this query starts on a leaf the list all the path to the big boss
select employee_id || '-' || first_name from employees
start with employee_id = 113
connect by prior manager_id = employee_id

Open in new window


So, this is how it works. You need to start at some point and you need to decide the direction you are going in the tree: from root to leaf or leaf to root.
Important thing: this table is a simplification of the employees table that came with Oracle XE 11g. If you need more information, my advice is to look for oracle manuals, where they will probably use the same example.

Back to your problem, I would change a little bit: join the 2 tables in another with and just then use the hierarchical query.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37734151
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...

http://www.experts-exchange.com/Database/Oracle/A_2450-Connect-by-prior.html
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now