Hierarchical Queries

http://www.techrepublic.com/article/understand-oracle-10gs-new-hierarchical-query-options/5800412

Oracle 10g adds new pseudo columns to the SQL Hierarchical Queries that use START WITH . . . CONNECT BY . . . clauses. For more than a decade, Oracle SQL has had the ability to cause a query to follow a hierarchical relationship. For instance, you could specify a starting condition and continue to child rows using one or more connection conditions.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm


START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

• The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.
  
• In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
  

  ... PRIOR expr = expr
  or
  ... expr = PRIOR expr
  
  

  If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. For example:
  

  CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
  CONNECT BY PRIOR employee_id = manager_id and 
             PRIOR account_mgr_id = customer_id ...
  
  

  PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
  PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.
  

Both the CONNECT BY condition and the PRIOR expression can take the form of an uncorrelated subquery. However, the PRIOR expression cannot refer to a sequence. That is, CURRVAL and NEXTVAL are not valid PRIOR expressions.
You can further refine a hierarchical query by using the CONNECT_BY_ROOT operator to qualify a column in the select list. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.