?
Solved

understanding hierarchial query in oracle

Posted on 2012-03-17
2
Medium Priority
?
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 1240 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

777 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