SQL & PL/SQL

I'm having problems in a select statement with connect by and start with that it's doing full table scan in a indexed table. The start with statement does a join by PK and still does FTS.

The select statement is this:

select cta.cod_conta_contabil
from ct_conta_contabil cta
where cta.tipo_conta = 'L'
connect by prior cta.cod_conta_contabil = cta.cod_conta_pai
start with cta.cod_conta_contabil = :b1

PK and index ICT_CONTACTB2 of ct_conta_contabil table, useds in explain plan:

alter table CT_CONTA_CONTABIL add constraint PKCT_CONTACTB primary key (COD_CONTA_CONTABIL)
create index ICT_CONTACTB2 on CT_CONTA_CONTABIL (COD_CONTA_PAI)

Explain plain on Oracle 9i:

SELECT STATEMENT, GOAL = RULE               
CONNECT BY WITH FILTERING               
  TABLE ACCESS BY INDEX ROWID   FINPAC   CT_CONTA_CONTABIL         
   INDEX RANGE SCAN   FINPAC   PKCT_CONTACTB        
  NESTED LOOPS               
   BUFFER SORT               
    CONNECT BY PUMP               
   TABLE ACCESS BY INDEX ROWID   FINPAC   CT_CONTA_CONTABIL         
    INDEX RANGE SCAN   FINPAC   ICT_CONTACTB2         
  TABLE ACCESS FULL   FINPAC   CT_CONTA_CONTABIL   

Explain plan on Oracle 10g:

SELECT STATEMENT, GOAL = CHOOSE
CONNECT BY WITH FILTERING               
  TABLE ACCESS BY INDEX ROWID   FINPAC   CT_CONTA_CONTABIL
   INDEX RANGE SCAN   FINPAC   PKCT_CONTACTB
  NESTED LOOPS               
   CONNECT BY PUMP               
   TABLE ACCESS BY INDEX ROWID   FINPAC   CT_CONTA_CONTABIL
    INDEX RANGE SCAN   FINPAC   ICT_CONTACTB2

I found some topics about this subject in Metalink, but didn't find the solution. As i see, this problem wasn't solved in 9i.

Anyone knows another way that make a hierarchical select without connect by or with connect by, but without FTS?

Regards,

Fabrício Pinto

Edited by: Fabrício Pinto on 20/04/2010 16:53

Edited by: Fabrício Pinto on 20/04/2010 17:03