Ø Hierarchical queries are queries
that are executed upon tables that contain hierarchical data.
Ø To execute the hierarchical queries,
we need the following clauses.
START WITH:
Ø It specifies the root rows of the
hierarchy.
CONNECT BY:
Ø It is used to specify the
relationship between parent rows and child rows of the hierarchy.
WHERE:
Ø It is used to restrict the rows
returned by the query without affecting other rows of the hierarchy.
Steps Followed By Oracle:
Ø Oracle selects the root rows of the
hierarchy, which satisfy the condition of the START WITH clause.
Ø Then oracle selects the child rows
of each ROOT ROW.
Ø Each child row must satisfy the
condition of the CONNECT BY clause, with respect to one of the ROOT ROWS.
Ø Oracle selects successive
generations of child rows by identifying the relation in the CONNECT BY clause.
Ø Oracle selects children by
evaluating the CONNECT BY condition with respect to the current parent row
selected.
Ø If the query contains a where
clause, oracle removes all rows from the hierarchy that do not satisfy the
condition of the where clause.
Restrictions:
Ø They cannot be used to perform
joins.
Ø They cannot select data from a view,
whose query perform a join.
Ø If Order by clause is used, then the
rows are returned as per the specification in the order by clause.
Ø To define hierarchical queries
properly we must use the following clauses.
- START WITH Clause.
- CONNECT BY Clause.
START WITh Clause:
Ø It identifies the rows to be used as
the root of a hierarchical query.
Ø It specifies a condition that the
roots must specify.
Ø If START WITH is omitted, oracle
uses all rows in the table as ROOT rows.
Ø A START WITH Condition can contain a
subquery.
CONNECT BY Clause:
Ø This Clause specifies the
relationship between parent and child rows, in a hierarchical query.
Ø This clause contains a condition
that defines relationship.
Ø This condition can be any condition
as defined by the syntax description.
Ø Within the condition, some part of
the condition must use the PRIOR operator, which refers to the parent row.
Ø The format of PRIOR Operator is
- PRIOR Expr Comparison operator Expr.
- Expr Comparison Operator PRIOR Expr.
Ø The Clause can contain other
conditions to further filter the rows selected by the query.
Ø It cannot contain a sub query.
Ex:
- Select Ename, Empno, Mgr, Job
From Emp CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, Mgr, Job
From Emp START WITH Job =’PRESIDENT’ CONNECT BY PRIOR Empno = MGR;
- Select Ename,Empno, Mgr, Job
From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, Mgr, Job,
Sal From Emp START WITH Sal = 5000 CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, Mgr, Job,
Sal From Emp START WITH Sal = (Select Max(Sal) From Emp ) CONNECT BY PRIOR
Empno = MGR;
- Select Ename, Empno, Mgr, Job,
Sal From Emp START WITH Sal = (Select Max(Sal) From Emp where Deptno =
(Select deptno from dept where Dname =’ACCOUNTING’ )) CONNECT BY PRIOR
Empno = MGR;
- Select Ename, Empno, Mgr, Job, Sal
From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR and Job =’MANAGER’;
New Features in Hierarchical Queries:
New
Operator:
CONNECT_BY_ROOT
New
Pseudo Columns:
CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
New
Function:
SYS_CONNECT_BY_PATH
New
Keywords:
NOCYCLE
SIBLINGS
CONNECT_BY_ROOT Operator:
Ø CONNECT_BY_ROOT is a UNARY Operator
that is valid only in hierarchical queries.
Ø We should qualify a column with this
operator, then oracle returns the column value using data from the ROOT Row.
Ø It extends the functionality of the
CONNECT BY [PRIOR] condition of hierarchical queries.
Restriction:
Ø We cannot specify this operator in
the START WITH Condition or the CONNECT BY condition.
Ex:
- Select Ename Name,
CONNECT_BY_ROOT(Ename) Boss From Emp START WITH Empno = 7839 CONNECT BY
PRIOR Empno = MGR;
SYS_CONNECT_BY_PATH Funtion:
Ø The function returns the path or a
column value from ROOT to node, with column values separated by ‘char’ for each
row returned by CONNECT BY condition.
Ø Can work on any data type CHAR or
VARCHAR2.
Ex:
- Select Ename,
SYS_CONNECT_BY_PATH(Ename, ‘/’) “path” From Emp START WITH Ename =’KING’ CONNECT
BY PRIOR Empno = MGR;
NOCYCLE Keyword:
Ø Cycles are not allowed in a true
tree structure. But some hierarchical data may contain cycles.
Ø To allow the “START WITH … CONNECT
BY… PRIOR“ construct to work properly even if cycles are present in the data
NOCYCLE is used.
Ø The NOCYCLE parameter in the CONNECT
BY Condition causes oracle to return the rows in spite of the recursive loop.
Ex:
- Select Ename,
SYS_CONNECT_BY_PATH(Sal, ‘/’) “path”
From Emp START WITH Ename =’KING’ CONNECT BY NOCYCLE PRIOR Empno =
MGR;
SIBLINGS Keyword:
Ø The keyword is valid only when we
specify the hierarchical query using CONNECT BY Clause.
Ø ORDER SIBLINGS BY clause preserves
any ordering specified in the hierarchical query clause.
Ø The ORDER BY clause finally gets
applied on the query.
Ø ORDER SIBLINGS BY clause is
generally used when we want to order rows of siblings of the same parent.
Ex:
- Select Ename, Empno, MGR From
Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, MGR From
Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR Order by Ename;
- Select Ename, Empno, MGR From
Emp START WITH Empno= 7839 CONNECT BY PRIOR Empno = MGR ORDER SIBLINGS BY
Ename;