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

Question

I have a MYTYPE table in Oracle 10g representing a tree structure that looks something like this:

ID | PARENTID | DETAIL

I would like to select all rows in MYTYPE that are descendants of a specific ID so that I can create queries elsewhere, for example:

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

What is an economical way to build a set of children, preferably without using PL / SQL?

Answer 1

Oracle did not support hierarchical ANSI syntax when using the recursive factoring subquery (CTE in SQL Server syntax) prior to 11g R2, so you need to use your own CONNECT BY syntax (supported since v2):

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

, .

:

• ASK TOM: "connect by"

Answer 2

ID,ParentID Adjacency List. (.. , , ), (.. ). , Oracle CONNECT BY , . , -.

, Hierarchy Bridge, LEVEL . ID,DescendantID, , DescentantID . LEVEL . , , .

, , Nested Set Materialized Path. . , , , . .

Answer 3

Oracle can execute recursive queries. Try exploring start with ... connect bysomething 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

Answer 4

Here are the details for the "connect by" functions in oracle. http://psoug.org/reference/connectby.html