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"

Answer 2

Managing hierarchical data using ID,ParentID columns in an RDBMS is known as the Adjacency List model. While very easy to implement and maintain (i.e. insert, update, delete), it's expensive to determine lineage (i.e. ancestors and descendants). As other answers already have written, Oracle's CONNECT BY will work, but this is an expensive operation. You may be better off representing your data differently.

For your case, the easiest solution might adding a what's called a Hierarchy Bridge table to your schema and adding a LEVEL column to your original table. The table has columns ID,DescendantID whereby selecting on ID gives all descendant records, and selecting by DescentantID gives all ancestor records. LEVEL is necessary on the base table to order records. In this way you make a tradeoff of expensive updates for cheap reads, which is what your question implies you want.

Other possibilities that involve changing your base data include Nested Set and Materialized Path representations. That offer similar tradeoffs of more expensive writes for much cheaper reads. For a complete list of options, pros and cons, and some implementation notes, see my previous question on the topic.

Answer 3

Here is the details for 'connect by' features in oracle. http://psoug.org/reference/connectby.html

Answer 4

Oracle can do recursive queries. Try looking into start with ... connect by, something like this:

Select *
from MYDETAIL
Starting with PARENTID= 1 --or whatever the root ID is
connect by PARENTID = prior ID

http://psoug.org/reference/connectby.html