Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

understanding hierarchial query in oracle

Posted on 2012-03-17
2
Medium Priority
?
297 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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