Re: Connect by START WITH+ IN subquery - query optimization - help !

From: <vSzemkel_at_o2.pl>
Date: Fri, 5 Jan 2007 10:40:54 +0100
Message-ID: <enl6f7$8b2$1@news.onet.pl>

Hi, Jonathan

Dirty fix did not work in my example:
It is 9.2.0.8 EE on Windows
The performance problem was fized in 10.2, but sometimes it's getting ora-600..

Is there any hint to improve this query plan?

   select mak_xx,nr_porz,level lvl from spacer_strona

       where nvl(dervlvl,0)<3
       start with mak_xx=125414 and nr_porz=0
     connect by mak_xx = prior derv_mak_xx and nr_porz = prior derv_nr_porz 

and prior dervlvl=3

Plan wykonywania

---

wiersze zostan? obciąte

          0                    SELECT STATEMENT Optimizer=CHOOSE (Cost=23684 
Card=2114671 Bytes=21146710)
          1                  0   FILTER
          2                  1     CONNECT BY (WITH FILTERING)
          3                  2       FILTER
          4                  3         TABLE ACCESS (FULL) OF 


'SPACER_STRONA' (Cost=23684 Card=2114671 Bytes=21146710)

          5                  2       HASH JOIN
          6                  5         CONNECT BY PUMP
          7                  5         TABLE ACCESS (FULL) OF 


'SPACER_STRONA' (Cost=23684 Card=2114671 Bytes=21146710)

          8                  2       TABLE ACCESS (FULL) OF 'SPACER_STRONA' 

(Cost=23684 Card=2114671 Bytes=21146710)

Statystyki

---

          0  recursive calls
          0  db block gets
     182546  consistent gets
     150479  physical reads
          0  redo size
        506  bytes sent via SQL*Net to client
        383  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Received on Fri Jan 05 2007 - 03:40:54 CST