Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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!!
0
mahjag
Asked:
mahjag
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
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
 
Wasim Akram ShaikCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now