Hierarchical or Recursive Queries

Ø  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;