Stessa query con risultati diversi

venerdì 29 maggio 2009 alle 29:23 | Pubblicato su SQL | 4 commenti
Tag: , , ,

Alcuni giorni fa mi è stato sottoposto una query molto particolare che si comportava in modo anomalo: facendo SELECT count(*) …. dava un numero, facendo SELECT * …. dava un numero diverso di record.  Analizzando un po’ la query ho subito notato che la differenza nei due casi era il piano di esecuzione (francamente spero non vi siano altre possibilità). Ho provato un pochino ad analizzare i piani di esecuzione ed ha trasformare la query ma senza capire molto. Oggi però sono riuscito, senza capire dove sta esattamente l’inghippo, a riprodurre il problema su uno degli schemi di esempio di Oracle.

Breve parentesi, con Oracle 10g gli schemi di esempio (HR,SH, OE, ecc.)  sono inclusi nel “companion CD”, oggi l’ho scaricato ed ho provato a lanciarne l’installazione, ma pare che per installare qualche KByte di script sia necessario installare oltre 700 MB di roba sulla macchina, spazio che sulla macchina di sviluppo dove volevo installare tali schemi non ho, allora mi sono estratto manualmente dal pacchettone di installazione solo gli script che mi servivano.

Fine della parentesi.

Come dicevo sono riuscito in qualche modo a riprodurre un caso analogo a quello segnalatomi dal nostro laboratorio di sviluppo, in cui in sostanza la stessa query da risultati diversi. Ho cercato fra gli schemi di esempio uno che avesse una struttura adatta a riprodurre il mio caso, lo schema che ho individuato è OE,  la query che ho scritto, inspirandomi non ad una logica sensata ma all’obbiettivo di riprodurre con il minimo sforzo il caso è questa:


select
 count(*)
 from
 products pi , inventories inv
 where
 pi.product_id=inv.product_id(+)
 and inv.warehouse_id(+) =
 case when
 exists
 (select ct.category_id
 from  categories_tab ct,
 product_prices pp
 where ct.category_id=pp.category_id
 and ct.category_id=pi.category_id)
 then to_number(11)
 else to_number(1)
 end

Sono stato molto fortunato, perché non avevo la forza di pensare molto e nonostante ciò in poco tempo sono riuscito a riprodurre il caso che volevo. Fra l’altro la logica originale non era poi così distante dal caso che ho inventato io perché si trattava proprio di una query su articoli, listini, magazzini ecc.

Questa è la versione che credo corretta:


OE@perseo10 > select
 2     count(*)
 3    from
 4    products pi , inventories inv
 5   where
 6   pi.product_id=inv.product_id(+)
 7   and inv.warehouse_id(+) =
 8    case when
 9     exists
 10      (select ct.category_id
 11      from  categories_tab ct,
 12             product_prices pp
 13      where ct.category_id=pp.category_id
 14             and ct.category_id=pi.category_id)
 15      then to_number(11)
 16      else to_number(1)
 17    end
 18  /

 COUNT(*)
----------
 288

Piano di esecuzione
----------------------------------------------------------
Plan hash value: 2199577315

-----------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |     1 |    21 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                     |     1 |    21 |            |          |
|   2 |   NESTED LOOPS OUTER    |                     |   288 |  6048 |     5   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER   |                     |   288 |  4032 |     5   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | PRODUCT_INFORMATION |   288 |  2016 |     5   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN   | PRD_DESC_PK         |     1 |     7 |     0   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN     | INVENTORY_IX        |     1 |     7 |     0   (0)| 00:00:01 |
|   7 |     NESTED LOOPS        |                     |    11 |   165 |     5   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN  | SYS_C0056077        |     1 |     2 |     0   (0)| 00:00:01 |
|   9 |      VIEW               | PRODUCT_PRICES      |    11 |   143 |     5   (0)| 00:00:01 |
|  10 |       SORT GROUP BY     |                     |    11 |    33 |     5   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL| PRODUCT_INFORMATION |    17 |    51 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 5 - access("D"."PRODUCT_ID"(+)="I"."PRODUCT_ID" AND
 "D"."LANGUAGE_ID"(+)=SYS_CONTEXT('USERENV','LANG'))
 6 - access("INV"."WAREHOUSE_ID"(+)=CASE  WHEN  EXISTS (SELECT 0 FROM  (SELECT
 "CATEGORY_ID" "CATEGORY_ID" FROM OE."PRODUCT_INFORMATION" "PRODUCT_INFORMATION" WHERE
 "CATEGORY_ID"=:B1 GROUP BY "CATEGORY_ID") "PP","CATEGORIES_TAB" "CT" WHERE
 "CT"."CATEGORY_ID"=:B2) THEN 11 ELSE 1 END  AND "I"."PRODUCT_ID"="INV"."PRODUCT_ID"(+))
 8 - access("CT"."CATEGORY_ID"=:B1)
 11 - filter("CATEGORY_ID"=:B1)

A questo punto, per forzare il cambiamento di piano di esecuzione (e di risultato) utilizzo un hint:


OE@perseo10 > select
 2    /*+ RULE */ count(*)
 3    from
 4    products pi , inventories inv
 5   where
 6   pi.product_id=inv.product_id(+)
 7   and inv.warehouse_id(+) =
 8    case when
 9     exists
 10      (select ct.category_id
 11      from  categories_tab ct,
 12             product_prices pp
 13      where ct.category_id=pp.category_id
 14             and ct.category_id=pi.category_id)
 15      then to_number(11)
 16      else to_number(1)
 17    end
 18  /

 COUNT(*)
----------
 36

Piano di esecuzione
----------------------------------------------------------
Plan hash value: 2133092042

---------------------------------------------------------
| Id  | Operation                 | Name                |
---------------------------------------------------------
|   0 | SELECT STATEMENT          |                     |
|   1 |  SORT AGGREGATE           |                     |
|*  2 |   FILTER                  |                     |
|   3 |    NESTED LOOPS OUTER     |                     |
|   4 |     NESTED LOOPS OUTER    |                     |
|   5 |      TABLE ACCESS FULL    | PRODUCT_INFORMATION |
|*  6 |      INDEX UNIQUE SCAN    | PRD_DESC_PK         |
|*  7 |     INDEX RANGE SCAN      | INVENTORY_IX        |
|   8 |      MERGE JOIN           |                     |
|*  9 |       INDEX UNIQUE SCAN   | SYS_C0056077        |
|* 10 |       FILTER              |                     |
|  11 |        VIEW               | PRODUCT_PRICES      |
|  12 |         SORT GROUP BY     |                     |
|  13 |          TABLE ACCESS FULL| PRODUCT_INFORMATION |
|  14 |    MERGE JOIN             |                     |
|* 15 |     INDEX UNIQUE SCAN     | SYS_C0056077        |
|* 16 |     FILTER                |                     |
|  17 |      VIEW                 | PRODUCT_PRICES      |
|  18 |       SORT GROUP BY       |                     |
|  19 |        TABLE ACCESS FULL  | PRODUCT_INFORMATION |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("INV"."WAREHOUSE_ID"(+)=CASE  WHEN  EXISTS (SELECT 0 FROM
 (SELECT "CATEGORY_ID" "CATEGORY_ID" FROM OE."PRODUCT_INFORMATION"
 "PRODUCT_INFORMATION" GROUP BY "CATEGORY_ID") "PP","CATEGORIES_TAB"
 "CT" WHERE "CT"."CATEGORY_ID"=:B1 AND
 "CT"."CATEGORY_ID"="PP"."CATEGORY_ID") THEN 11 ELSE 1 END )
 6 - access("D"."PRODUCT_ID"(+)="I"."PRODUCT_ID" AND
 "D"."LANGUAGE_ID"(+)=SYS_CONTEXT('USERENV','LANG'))
 7 - access("INV"."WAREHOUSE_ID"(+)=CASE  WHEN  EXISTS (SELECT 0 FROM
 (SELECT "CATEGORY_ID" "CATEGORY_ID" FROM OE."PRODUCT_INFORMATION"
 "PRODUCT_INFORMATION" GROUP BY "CATEGORY_ID") "PP","CATEGORIES_TAB"
 "CT" WHERE "CT"."CATEGORY_ID"=:B1 AND
 "CT"."CATEGORY_ID"="PP"."CATEGORY_ID") THEN 11 ELSE 1 END  AND
 "I"."PRODUCT_ID"="INV"."PRODUCT_ID"(+))
 9 - access("CT"."CATEGORY_ID"=:B1)
 10 - filter("CT"."CATEGORY_ID"="PP"."CATEGORY_ID")
 15 - access("CT"."CATEGORY_ID"=:B1)
 16 - filter("CT"."CATEGORY_ID"="PP"."CATEGORY_ID")

Note
-----
 - rule based optimizer used (consider using cbo)

Nel caso originale in realtà non era necessario alcun hint, ma era sufficente passare da count(*) a * nella select list per forzare l’ottimizzatore ad utilizzare un piano di esecuzione diverso ed a produrre un “result set” diverso.

Il punto sta nella parte:


and inv.warehouse_id(+) =
 case

Il piano di esecuzione corretto prevede solo “NESTED LOOPS OUTER” quello che produce un risultato “non corretto” (in pratica non applica l’operatore (+) ) usa in questo caso un MERGE JOIN. Nella query originale, molto più complessa per la verità, non vi sono MERGE JOIN c’è solo un HASH OUTER JOIN, ne riporto un tratto:

—————————————-
| Id  | Operation                      |
—————————————-
|   0 | SELECT STATEMENT               |
|   1 |  SORT ORDER BY                 |
|*  2 |   FILTER                       |
|*  3 |    HASH JOIN OUTER             |
|*  4 |     HASH JOIN                  |
|*  5 |      TABLE ACCESS FULL         |
|*  6 |      TABLE ACCESS FULL         |
|   7 |     TABLE ACCESS FULL          |
|   8 |    NESTED LOOPS                |
|*  9 |     TABLE ACCESS BY INDEX ROWID|
|* 10 |      INDEX RANGE SCAN          |
|* 11 |     INDEX UNIQUE SCAN          |
—————————————-

Ora, purtroppo non ho ancora studiato bene i piani di esecuzione, ma sotto compare:

Predicate Information (identified by operation id):
—————————————————

2 – filter(“campo_fk”(+)=CASE  WHEN  EXISTS

e non mi è chiaro se questo filtro viene applicato dopo la HASH JOIN OUTER della riga 3.

Il database originale è 11g, quello che ho usato io per il test è 10gR2, il caso originale l’ho riprodotto anche su 9iR2. La query fa’ un utilizzo  un po’ ardito dell’operatore (+) ma è sintatticamente corretta e tradurla in sintassi ANSI JOIN non mi pare cosa fattibile (perché in effetti una join non è… o si?) soprattutto non mi sento in grado di farlo con l’assoluta certezza dell’equivalenza.

La documentazione Oracle sull’operatore (+) non dice molto, qualcosa in più lo dicono qui, e la mia impressione in effetti è che il costrutto è molto simile a una “lateral view” un concetto che da un po’ sto cercando di digerire ma che sta ancora li, non ben definito nella mia testa.

4 commenti »

RSS feed for comments on this post. TrackBack URI

  1. interessante, appena ho un po di tempo voglio ripeterlo anche io questo test….

    Alessandro

  2. hai provato settando

    “_optimizer_sortmerge_join_enabled” = false

    ?

    Alessandro

  3. l’ho riprodotto si di una 10.2.0.1.0 su Linux RedHat con una variante, utilizzando l’hint di RULE ottengo 0 mentre in CBO ottengo 288..

    E’ decisamente un bug credo, insomma differenti piani di esecuzione non dovrebbero mai precludere il risultato di una query anche se qualche volta succede, ma quando avviene si è davanti ad un bug

    Alessandro

  4. Non so, l’operatore (+) non è molto documentato mi sembra, quindi anche se come giustamente dici è un comportamento che non si deve verificare secondo me Oracle potrebbe dire che è un comportamento previsto dell’operatore🙂


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: