CONNECT BY (PUMP)

Description

Participates in a hierarchical self-join

Versions

This operation was introduced in Oracle 8.1.7

This operation is implemented in the following versions

8.1.7
9.0.1
9.2.0
10.1.0
10.2.0

In Oracle 8.1.7 only, the session parameter "_new_connect_by_enabled" must be set to true to generate plans using this operation

In Oracle 9.0.1 and above it is the default behaviour

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table and index definitions

    CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);

The table does not need to be analysed

In Oracle 8.1.7 only, the session parameter "_new_connect_by_enabled" must be set to true to generate plans using this operation

   ALTER SESSION SET "_new_connect_by_enabled" = TRUE;

In Oracle 8.1.7 only, 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 (WITH FILTERING)
   2   1     FILTER
   3   2       TABLE ACCESS (FULL) OF 'T1'
   4   1     HASH JOIN
   5   4       CONNECT BY PUMP
   6   4       TABLE ACCESS (FULL) OF 'T1'

In Oracle 9.0.1 and above, 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 (WITH FILTERING)
   2   1     NESTED LOOPS
   3   2       TABLE ACCESS (FULL) OF 'T1'
   4   2       TABLE ACCESS (BY USER ROWID) OF 'T1'
   5   1     NESTED LOOPS
   6   5       BUFFER (SORT)
   7   6         CONNECT BY PUMP
   8   5       TABLE ACCESS (FULL) OF 'T1'

In Oracle 10.2 the following plan was generated

   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY (WITH FILTERING)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'T1' 
   4    1     HASH JOIN
   5    4       CONNECT BY PUMP
   6    4       TABLE ACCESS (FULL) OF 'T1'
   7    1     TABLE ACCESS (FULL) OF 'T1'