Re: performance problems with hierarchical query and outer join

Mladen Gogala wrote:

> May be, if you could put tables FRN and MOV in a cluster and cluster
> them on all_cod column?

Thank you Mladen for your response, unfortutately the problem seem to be related to the fact that the CONNECT BY contains an OR.

Actually, I could modify my data in a way that would avoid the outer join at all:

select

   *
from

   mov0
start with

   %${some-start-condition}
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 )

   ( mov0.lotto_uscita_id = prior mov0.lotto_entrata_id ) )

I tried to create two new indexes on MOV0, one on LOTTO_USCITA_ID and one on LOTTO_ENTRATA_ID. I gathered statistics on the two new indexes and re-gathered statistics on the previous indexes and on the MOV0 table.

Here's the plan table of this query:

Plan Table

| Operation                 |  Name              |  Rows | Bytes|  Cost
------------------------------------------------------------------------
| SELECT STATEMENT          |                    |    41K|    3M|     86
|  CONNECT BY WITHOUT FILTER|                    |       |      |
|   TABLE ACCESS FULL       |MOV0                |    41K|    3M|     86
------------------------------------------------------------------------

If I comment out either one of the CONNECT BY clauses the query is fast, but with the two clauses together the performance is awful.

Any suggestion? 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