CONNECT BY

Description

Performs a hierarchical self-join

Versions

This operation is implemented in the following versions

7.3.4
8.0.4
8.0.5
8.0.6
8.1.5
8.1.6
8.1.7
9.0.1
9.2.0
10.1.0
10.2.0

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

In Oracle 9.0 and above, the CONNECT BY operation is no longer the default behaviour for the CONNECT BY statement. In order to force a CONNECT BY operation the unsupported parameter "_old_connect_by_enabled" must be set to true e.g. 

   ALTER SESSION SET "_old_connect_by_enabled" = TRUE;

This example requires the following table and index definitions 

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

The table does not need to be analysed

The statement 

    SELECT c1,c2 FROM t1
    CONNECT BY c1 = PRIOR c2
    START WITH c1 = 0;

generates the following execution plan 

   0     SELECT STATEMENT Optimizer=CHOOSE
   1   0   CONNECT BY
   2   1     TABLE ACCESS (FULL) OF 'T1'
   3   1     TABLE ACCESS (BY USER ROWID) OF 'T1'
   4   1     TABLE ACCESS (FULL) OF 'T1'