connect by

From: Peter Karl Lichtenwagner <peter_at_monochrom.at>
Date: Thu, 31 Aug 2000 21:28:45 GMT
Message-ID: <h6Ar5.27517$Ty1.2003452@news.chello.at>

everyone knows the problem:

in a connect by tree the leaves are usually not ordered:

SQL> select rpad(' ',(level-1))||ename from scott.emp   2 start with job='PRESIDENT'
  3 connect by prior empno = mgr;

RPAD('',(LEVEL-1))||ENAME

---

KING
 JONES
  SCOTT
   ADAMS
  FORD
   SMITH
 BLAKE
  ALLEN
  WARD
  MARTIN
  TURNER
  JAMES
 CLARK
  MILLER 14 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 CONNECT BY

   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY USER ROWID) OF 'EMP'
   4    1     TABLE ACCESS (FULL) OF 'EMP'

trying to solve this with an order by clause just messes up the result:

SQL> select rpad(' ',(level-1))||ename from scott.emp   2 start with job='PRESIDENT'
  3 connect by prior empno = mgr
  4 order by ename;

RPAD('',(LEVEL-1))||ENAME

---

   ADAMS
  ALLEN
 BLAKE
 CLARK
  FORD
  JAMES
 JONES
KING
  MARTIN
  MILLER
  SCOTT
   SMITH
  TURNER
  WARD 14 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (ORDER BY)

   2    1     CONNECT BY
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    2       TABLE ACCESS (BY USER ROWID) OF 'EMP'
   5    2       TABLE ACCESS (FULL) OF 'EMP'

trying to fool the oracle server and put the order by clause into a subquery, which is (sometimes) allowed in 8.1.6, shows that it is not in this particular case:

SQL> select rpad(' ',(level-1))||ename from (select empno, mgr, job, ename from scott.emp order by ename)   2 start with job='PRESIDENT'
  3 connect by prior empno = mgr;
select rpad(' ',(level-1))||ename from (select empno, mgr, job, ename from scott.emp order by ename)

                                                                                 *

ERROR at line 1:
ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc.

hmmmm

but:

SQL> alter session set "_new_connect_by_enabled"=TRUE;

Session altered.

and voila:

SQL> select rpad(' ',(level-1))||ename from (select empno, mgr, job, ename from scott.emp order by ename)   2 start with job='PRESIDENT'
  3 connect by prior empno = mgr;

RPAD('',(LEVEL-1))||ENAME

---

KING
 BLAKE
  ALLEN
  JAMES
  MARTIN
  TURNER
  WARD
 CLARK
  MILLER
 JONES
  FORD
   SMITH
  SCOTT
   ADAMS 14 rows selected.

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=82 Bytes=3198)    1 0 CONNECT BY (WITH FILTERING)

   2    1     FILTER
   3    2       COUNT
   4    3         VIEW (Cost=3 Card=82 Bytes=3198)
   5    4           SORT (ORDER BY) (Cost=3 Card=82 Bytes=3198)
   6    5             TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=3198)
   7    1     HASH JOIN
   8    7       CONNECT BY PUMP
   9    7       COUNT
  10    9         VIEW (Cost=3 Card=82 Bytes=3198)
  11   10           SORT (ORDER BY) (Cost=3 Card=82 Bytes=3198)
  12   11             TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=3198)

any comments?
does anyone know if setting this hidden parameter has any drawbacks?

peter

---

Der Flügelflagel gaustert
durchs Wiruwaruwolz,
die rote Fingur plaustert
und grausig gutzt der Golz.
[chm] Received on Thu Aug 31 2000 - 16:28:45 CDT