Selecting all descendant rows from an Oracle table representing a tree structure

Question

I have a table MYTYPE in Oracle 10g representing a tree structure, which is something like this:

ID | PARENTID | DETAIL

I would like to select all rows in MYTYPE which are descendants of a particular ID, such that I can create queries elsewhere such as:

SELECT * 
  FROM MYDETAIL 
 WHERE MYTYPEID IN [all MYTYPE which are descendants of some ID];

What is a cost-efficient way of building the descendant set, preferably without using PL/SQL?

Answer 1

Oracle didn't support the ANSI hierarchical syntax of using a recursive Subquery Factoring (CTE in SQL Server syntax) until 11g R2, so you have to use Oracle's native CONNECT BY syntax (supported since v2):

   SELECT t.*
      FROM MYTABLE t
START WITH t.parentid = ?
CONNECT BY PRIOR t.id = t.parentid

Replace the question mark with the parent you want to find the hierarchical data based on.

Reference:

• ASK TOM: "connect by"