hierarchical query + outer join performance problems

From: Cris Carampa <cris119_at_operamail.com>
Date: Wed, 06 Jul 2005 10:33:04 +0200
Message-ID: <42cb975b$0$336$5fc30a8@news.tiscali.it>

Hello, the following hierarchical query gives acceptable execution times:

select

rownum,

   mpr.piva_produttore,
   mpr.lotto_spedizione_id,
   mpr.piva_fornitore,
   mpr.lotto_spedizione_id_fornitore,
   mpr.des_materia_prima_produttore,
   mpr.data_spedizione,
   mpr.nro_bolla,

level as lvl
from

mpr
start with

   mpr.piva_produttore = :x and
   mpr.lotto_spedizione_id = :y and
   mpr.prodotto_id = :z

connect by

   mpr.piva_produttore = prior mpr.piva_fornitore and
   mpr.lotto_spedizione_id = prior mpr.lotto_spedizione_id_fornitore and
   mpr.prodotto_id = prior mpr.prodotto_id_materia_prima

;

SQL> @q-hq

4 rows selected.

Elapsed: 00:00:00.01

Unfortunately I need to pick up some data from table LOT, who must outer-joined with table MPR. If I do that, the performance decreases dramatically:

select

rownum,

   mpr.piva_produttore,
   mpr.lotto_spedizione_id,
   lot.caz_id,
   mpr.piva_fornitore,
   mpr.lotto_spedizione_id_fornitore,

   nvl(lot.des_prodotto_produttore,mpr.des_materia_prima_produttore),    lot.data_produzione,
   nvl(lot.data_spedizione,mpr.data_spedizione),    nvl(lot.nro_bolla,mpr.nro_bolla),
   level as lvl
from

lot,
mpr
where

outer join between lot and mpr lot.piva_produttore(+) = mpr.piva_fornitore and lot.lotto_spedizione_id(+) = mpr.lotto_spedizione_id_fornitore and lot.prodotto_id(+) = mpr.prodotto_id_materia_prima start with mpr.piva_produttore = :x and mpr.lotto_spedizione_id = :y and mpr.prodotto_id = :z connect by mpr.piva_produttore = prior mpr.piva_fornitore and mpr.lotto_spedizione_id = prior mpr.lotto_spedizione_id_fornitore and mpr.prodotto_id = prior mpr.prodotto_id_materia_prima ;

SQL> @q3

4 rows selected.

Elapsed: 00:00:10.31

Here's the query plan:

| SELECT STATEMENT | | 112K| 12M| 832
| COUNT | | | |
| CONNECT BY WITH FILTERIN| | | |
| FILTER | | | |
| COUNT | | | |
| HASH JOIN OUTER | | 112K| 12M| 832
| TABLE ACCESS FULL |MPR | 112K| 7M| 208
| TABLE ACCESS FULL |LOT | 35K| 1M| 54
| HASH JOIN | | | |
| CONNECT BY PUMP | | | |
| COUNT | | | |
| HASH JOIN OUTER | | 112K| 12M| 832
| TABLE ACCESS FULL |MPR | 112K| 7M| 208
| TABLE ACCESS FULL |LOT | 35K| 1M| 54

How can I avoid the FTS on MPR? I tried to create an index on the joined columns but the optimizer seem to ignore it.

I'm using Oracle 9.0.1 on Linux.

Thanks in advance. Kind regards,

-- 
Cris Carampa (cris119_at_operamail.com)
"E` evidente che siamo di fronte allo sfruttamento opportunistico
  di quella quota di astensioni fatalmente derivanti da disinteresse
  o indifferenza." (Gustavo Zagrebelsky)

Received on Wed Jul 06 2005 - 03:33:04 CDT