Re: how to do when joints forbidden in hierarchical queries

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Sep 1998 15:51:54 GMT
Message-ID: <3612afdb.12491922@192.86.155.100>

A copy of this was sent to "Grinberg L." <leon_at_grant.kharkov.ua> (if that email address didn't require changing) On Mon, 28 Sep 1998 16:30:14 +0300, you wrote:

>
>Christopher Beck wrote in message
><360c01ee.24633501_at_dcsun4.us.oracle.com>...
>
>>select a.l, a.ename, d.dname, d.loc
>> from dept d,
>> ( select level l, ename, deptno
>> from emp
>> connect by mgr=prior empno
>> start with mgr is null ) a
>> where a.deptno = d.deptno
>>/
>
>
>But this SELECT doesn't return rows in "hierachical" order.
>IMHO you must use PL/SQL.
>

well, thats not entirely accurate. If

the inline view drives the query
there exists an index on dept( deptno )

then, this query will return data in hierarchical order. It all depends on the plan, this is true. Consider tho:

SQL> select lpad( ' ',2*(a.l-1)) || a.ename ename, d.dname, d.loc 2 from

  3         ( select level l, ename, deptno
  4             from emp
  5          connect by mgr=prior empno
  6            start with mgr is null ) a,

  7 dept d
  8 where a.deptno = d.deptno
  9 /

ENAME                DNAME          LOC
-------------------- -------------- -------------
KING                 ACCOUNTING     NEW YORK
  JONES              RESEARCH       DALLAS
    SCOTT            RESEARCH       DALLAS
      ADAMS          RESEARCH       DALLAS
    FORD             RESEARCH       DALLAS
      SMITH          RESEARCH       DALLAS
  BLAKE              SALES          CHICAGO
    ALLEN            SALES          CHICAGO
    WARD             SALES          CHICAGO
    MARTIN           SALES          CHICAGO
    TURNER           SALES          CHICAGO
    JAMES            SALES          CHICAGO
  CLARK              ACCOUNTING     NEW YORK
    MILLER           ACCOUNTING     NEW YORK

14 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS

   2    1     VIEW
   3    2       CONNECT BY
   4    3         TABLE ACCESS (FULL) OF 'EMP'
   5    3         TABLE ACCESS (BY USER ROWID) OF 'EMP'
   6    3         TABLE ACCESS (FULL) OF 'EMP'
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   8    7       INDEX (RANGE SCAN) OF 'DEPT_IDX' (NON-UNIQUE)

Since it does the inline view and uses that as the driving table, it works just dandy and will be much faster then using PL/SQL to do the lookup.

YMMV -- this is not true in every case, it will depend on how the underlying tables are indexed and what access method is used.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 28 1998 - 10:51:54 CDT