"start with" non using index

From: Krzysztof Pozorek <access_at_vis.pl>
Date: Mon, 4 Dec 2006 14:44:34 +0100
Message-ID: <el18m6$ssq$1@news.onet.pl>

The query is:
select mak_xx,nr_porz,level lvl from spacer_strona

    start with mak_xx=125414 and nr_porz=0   connect by mak_xx = prior derv_mak_xx

In 9.2.0.3 Windows it is fast:
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=8)   CONNECT BY (WITH FILTERING)
    NESTED LOOPS

      TABLE ACCESS (CLUSTER) OF 'SPACER_STRONA' (Cost=2 Card=1 Bytes=6)
        INDEX (UNIQUE SCAN) OF 'IND_C_MAKIETA' (NON-UNIQUE) (Cost=1 
Card=2114671)
      TABLE ACCESS (BY USER ROWID) OF 'SPACER_STRONA'
    NESTED LOOPS
      BUFFER (SORT)
        CONNECT BY PUMP
      TABLE ACCESS (CLUSTER) OF 'SPACER_STRONA' (Cost=2 Card=1 Bytes=8)
        INDEX (UNIQUE SCAN) OF 'IND_C_MAKIETA' (NON-UNIQUE) (Cost=1 

Card=2114671)

In 9.2.0.8, 10.2.0.2 Windows it is slow: SELECT STATEMENT Optimizer=CHOOSE (Cost=23684 Card=2114671 Bytes=16917368)   CONNECT BY (WITH FILTERING)
    FILTER
      TABLE ACCESS (FULL) OF 'SPACER_STRONA' (Cost=23684 Card=2114671 Bytes=16917368)

    HASH JOIN

      CONNECT BY PUMP
      TABLE ACCESS (FULL) OF 'SPACER_STRONA' (Cost=23684 Card=2114671 

Bytes=16917368)

    TABLE ACCESS (FULL) OF 'SPACER_STRONA' (Cost=23684 Card=2114671 Bytes=16917368)

I re-computed index and cluster statistics I tried many different hints, no luck as far as now.

Any suggestions?

Regards,

   Marcin Received on Mon Dec 04 2006 - 07:44:34 CST