Hierachial Queries

SYS_CONNECT_BY_PATH

This clause is used only in the hierarchial queries to retrieve the path form the root to the leaf node.

Eg:

employees table :

empid      name      managerid
100         aparna
101         anusha     aparna
102         prema      aparna
103        ambika      anusha
104        anupama   anusha

select sys_connect_by_path(name,'/') 

from employees

start with name ='aparna'

connect by empid = managerid

will return :

/aparna
/aparna/anusha
/aparna/anusha/ambika
/aparna/anusha/anupama
/aparna/prema

Level:

select empid,name ,level 

from employees 

connect by prior empid=managerid 

will return

100 Aparna  1
101 Anusha  2
102 Prema   2
103 ambika  3
104 anupama 3


CONNECT_BY_ISCYCLE:

This will return 1 if there is a cycle in the relationships :

Eg:

assume aparna is the leader

anusha heads departemtn 10

now am issuing an update statement as follows:

update employees
set  managerid=101 where empid=100;

this update will make anusha as the manager of aparna

intirn anusha heads another department which is ten.

This creats a cycle which will be indicated by conect_by_iscycle parameter .

select empid,level,sys_connect_by_path(name,'/') as path,

connect_by_iscycle  cycle

from employees

start with name='aparna'

connect by prior empid=managerid 

where level<=3

 CONNECT_BY_ISLEAF:

 this clause returns 1 if the curent node is the leaf node .


CONNECT_BY_ROOT: 

 The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

The following example uses a GROUP BY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:

SELECT name, SUM(salary) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT last_name as name, Salary
      FROM employees
      WHERE department_id = 110
      CONNECT BY PRIOR employee_id = manager_id)
      GROUP BY name;

 LEVEL:

        This is pseudo column which will return the level.This also requires the connect by clause  .

Eliminating Nodes and Branches from a Hierarchical Query

We can eliminate nodes by putting a where clause in the form clause and we can eliminate branches by putting a where clause in the connect by clause .

we can spacify any number of conditions in the where clause as well as in the connect by clause of a hierarchial query.

select empid,name, managerid 

from employees 

where emp_id not in (100,101) 

connect by prior empid=managerid 

or if we have to eliminate a full node form processing the following query will be syueful :

select empid,name, managerid 

from employees 

where emp_id not in (100,101) 

connect by prior empid=managerid and name !='Anusha'

 

 

 

index scans

range scan :

when the indexed column is  referenced in the where clause .

index fast full scan :

when the column s in the select clause is all indexed

index full scan :

it scans the index blocks fully in the unsorted manner

index skip scan :

this is used when there is a composit index and the second column is referenced .

eg: cretae table t (a number,b number,c number);

create index a_idx on t(a);
create index b_idx on t(b);

select * from t where b=12 (index skip scan)

select * form t wher ea=10 and b=12 (range scan)

select a,b from t (index fast full scan or full scan )