performance problems with hierarchical query and outer join

Good morning, Oracle 9.0.1.3 on SUSE SLES7 here.

The following hierarchical query based only upon the MOV0 table executes in less than a second:

select

   mov0.*
from

   mov0
start with

   ${some start condition on mov0}
connect by
( mov0.all_cod=prior mov0.all_cod and mov0.zona_destinazione_id=prior

mov0.zona_provenienza_id and mov0.box_destinazione_id=prior 
mov0.box_provenienza_id and mov0.fase_destinazione_dat_dal=prior 
mov0.fase_provenienza_dat_dal and mov0.movimento_data<=prior 
mov0.movimento_data )

That's because I have an unique constraint (and therefore an index) on MOV0 defined as follows:

alter table mov0
add constraint mfb_un01
unique
(all_cod,movimento_data,tipo_movimento_id,causa_movimento_id,lotto_entrata_id,zona_provenienza_id,box_provenienza_id,fase_provenienza_dat_dal,zona_destinazione_id,box_destinazione_id,fase_destinazione_dat_dal,lotto_uscita_id)
;

The optimizer is smart enough to use this index in the CONNECT BY, so the query is very fast.

Unfortunately a customer asked to add a second CONNECT BY clause based on another table outer-joined with MOV0. I had to rewrite the query this way:

select

   mov0.*
from

   FRN,
   MOV0
where

   frn.all_cod(+) = mov0.all_cod and
   frn.lotto_entrata_id(+) = mov0.lotto_entrata_id start with

   ${some start condition on mov0}
connect by

   ( mov0.all_cod = prior mov0.all_cod and mov0.zona_destinazione_id = prior mov0.zona_provenienza_id and mov0.box_destinazione_id = prior

mov0.box_provenienza_id and mov0.fase_destinazione_dat_dal = prior 
mov0.fase_provenienza_dat_dal and mov0.movimento_data <= prior 
mov0.movimento_data )

   OR
   ( mov0.all_cod = prior frn.all_cod_fornitore and mov0.lotto_uscita_id = prior frn.lotto_uscita_id_fornitore )
;

I explained this query and I have noticed that the optimizer perform a CONNECT BY WITHOUT FILTERING and he does't use the MFB_UN01 index anymore.

There are only 41050 rows in the MOV0 table but the performance of this query is awful.

Any suggestion to improve performance?

Thank you. Kind regards,

-- 
Cris Carampa (spamto:cris119_at_operamail.com)

potevo chiedere come si chiama il vostro cane
il mio è un po' di tempo che si chiama Libero