Different comportement analyseur 11g

Version imprimable

Bonjour,

Voici un test case intéressant:

La Table de test:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
drop table ZZ_TAB1;
create TABLE ZZ_TAB1				
(                                               
  DV_ID  INTEGER  NOT NULL,                                                                                                                                                                                           
  JE_ID  INTEGER  NOT NULL,                                                                                                                                                                                           
  PS_ID  INTEGER  NOT NULL,                                                                                                                                                                                           
  P_ID   INTEGER  NOT NULL,                                                                                                                                                                                           
  SP_ID  INTEGER  NOT NULL,                                                                                                                                                                                           
  JET_ID INTEGER  NOT NULL,                                                                                                                                                                                           
  JE_NUM INTEGER,                                                                                                                                                                                                     
  Rev_Num  INTEGER)
TABLESPACE users
PARTITION BY LIST (DV_ID)
(  
  PARTITION P002 VALUES (2)
    NOLOGGING
    NOCOMPRESS
,  
  PARTITION P032 VALUES (32)
    NOLOGGING
    NOCOMPRESS
,  
  PARTITION P035 VALUES (35)
    NOLOGGING
    NOCOMPRESS
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
Insert into ZZ_Tab1 Values (32,2590376,1,5,1,2,138,0);                      
Insert into ZZ_Tab1 Values (32,2590540,1,5,1,2,138,1);                      
Insert into ZZ_Tab1 Values (32,2590542,1,5,1,2,138,2);                      
Insert into ZZ_Tab1 Values (32,2590544,1,5,1,2,138,3);                      
Insert into ZZ_Tab1 Values (2,2590376,1,5,1,2,138,0);                      
Insert into ZZ_Tab1 Values (2,2590540,1,5,1,2,138,1);                      
Insert into ZZ_Tab1 Values (2,2590542,1,5,1,2,138,2);                      
Insert into ZZ_Tab1 Values (2,2590544,1,5,1,2,138,3);                      
Insert into ZZ_Tab1 Values (35,2590376,1,5,1,2,138,0);                      
Insert into ZZ_Tab1 Values (35,2590540,1,5,1,2,138,1);                      
Insert into ZZ_Tab1 Values (35,2590542,1,5,1,2,138,2);                      
Insert into ZZ_Tab1 Values (35,2590544,1,5,1,2,138,3);

Le phénomène ne se produit pas si la table n'est pas partitionée.

Voici la requête:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
      SELECT aa.JE_ID, aa.Rev_Num
                FROM ZZ_TAB1 aa
                WHERE aa.DV_ID = 32
                  AND aa.JE_NUM = 138   
                  AND aa.PS_ID = 1
                  AND aa.P_ID = 5
                  AND aa.SP_ID = 1
                  AND aa.Rev_Num =
                         (SELECT MAX (je.Rev_Num)
                            FROM ZZ_TAB1 je
                           WHERE je.DV_ID = aa.DV_ID
                             AND je.JE_NUM =aa.JE_NUM
                             AND je.P_ID = aa.P_ID
                             AND je.SP_ID = aa.SP_ID
                             AND je.PS_ID = aa.PS_ID);

Le résultat en 11g 11.1.0.7 retourne 0 lignes.
En 9i, la ligne avec Rev_Num = 3 est renvoyée.

Je suis partagé entre le fait de considérer le comportement comme normal ou comme un bug.

1) Normal car la sous requête utilise dans sa clause where des colonnes qui n'ont pas été sélectionnées par la requête parente.

Le truc étrange est que ceci fonctionne en 11g

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
      SELECT aa.JE_ID, aa.Rev_Num
                FROM ZZ_TAB1 aa
                WHERE aa.DV_ID = 32
                  AND aa.JE_NUM = 138   
                  AND aa.PS_ID = 1
                  AND aa.P_ID = 5
                  AND aa.SP_ID = 1
                  AND aa.Rev_Num =
                         (SELECT MAX (je.Rev_Num)
                            FROM ZZ_TAB1 je
                           WHERE je.DV_ID = 32
                             AND je.JE_NUM =aa.JE_NUM
                             AND je.P_ID = aa.P_ID
                             AND je.SP_ID = aa.SP_ID
                             AND je.PS_ID = aa.PS_ID);

2) Pas normal car si il a besoin d'une colonne pour satisfaire une sous requête, le plus simple est que l'optimiseur nous le signale.
Par exemple l'erreur "ORA-01785: ORDER BY item must be the number of a SELECT-list expression"

Quel est votre avis?

Merci
Jko

Je n'arrive pas à le reproduire :

Code:

1
2
3
4
5
6
7
8
select * from sys.v_$version;
 
BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE	11.1.0.7.0	Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select aa.je_id, aa.rev_num
  from zz_tab1 aa
 where aa.dv_id   = 32
   and aa.je_num  = 138
   and aa.ps_id   = 1
   and aa.p_id    = 5
   and aa.sp_id   = 1
   and aa.rev_num = 
         (select max(je.rev_num)
            from zz_tab1 je
           where je.dv_id  = aa.dv_id
             and je.je_num = aa.je_num
             and je.p_id   = aa.p_id
             and je.sp_id  = aa.sp_id
             and je.ps_id  = aa.ps_id);
 
JE_ID	REV_NUM
2590544	3

31/03/2010, 13h21
jkofr

J'ai 2 instances sous AIX.

Sur l'une d'entre elle, je reproduis l'erreur mais pas sur l'autre.8O
A noter que ces 2 instances on été migrées a partir d'une 9.2.0.8.

J'ai aussi une instance sous Windows 2008 R2 et ca fonctionne aussi.

Un paramètre?

jko
31/03/2010, 13h27
sgora

Peut-on avoir le plan d'exécution (le vrai, le row source operation) dans les deux cas ?

Instance qui pose problème

Code:

1
2
3
4
5
6
7
8
9
10
 
		Operation				Optimizer     Cost   Card     Bytes   Part Start   Part Stop Part ID		Filter
SELECT STATEMENT					ALL_ROWS	4	1	173					
 NESTED LOOPS								4	1	173					
  PARTITION LIST(SINGLE)						2	1	104	2		2	2		
   TABLE ACCESS(FULL) ZZ_TAB1						2	1	104	2		2	3		"AA"."JE_NUM"=138 AND "AA"."PS_ID"=1 AND "AA"."P_ID"=5 AND "AA"."SP_ID"=1
  VIEW PUSHED PREDICATE SYS.VW_SQ_1					2	1	69						"AA"."REV_NUM"="MAX(JE.REV_NUM)"
   SORT(GROUP BY)							2	1	143					
    PARTITION LIST(SINGLE)						2	1	143	KEY	KEY	6		
     TABLE ACCESS(FULL) ZZ_TAB1						2	1	143	KEY	KEY	6			"JE"."JE_NUM"=138 AND "JE"."PS_ID"=1 AND "JE"."P_ID"=5 AND "JE"."SP_ID"=1

Instance qui fonctionne

Code:

1
2
3
4
5
6
7
8
9
10
11
 
		Operation				Optimizer     Cost   Card     Bytes   Part Start   Part Stop Part ID		Filter
SELECT STATEMENT					ALL_ROWS	4	1	248					
 NESTED LOOPS								4	1	248					
  PARTITION LIST(SINGLE)						2	1	169	2		2	2		
   TABLE ACCESS(FULL) ZZ_TAB1						2	1	169	2		2	3		"AA"."JE_NUM"=138 AND "AA"."PS_ID"=1 AND "AA"."P_ID"=5 AND "AA"."SP_ID"=1
  VIEW PUSHED PREDICATE SYS.VW_SQ_1					2	1	79						"AA"."REV_NUM"="MAX(JE.REV_NUM)"
   SORT(GROUP BY)							2	1	143						
    FILTER																32="AA"."DV_ID"
     PARTITION LIST(SINGLE)						2	1	143	2		2	7		
      TABLE ACCESS(FULL) ZZ_TAB1					2	1	143	2		2	8		"JE"."JE_NUM"=138 AND "JE"."PS_ID"=1 AND "JE"."P_ID"=5 AND "JE"."SP_ID"=1

31/03/2010, 13h55
sgora

Ca ressemble fortement à un bug, j'en ai déjà rencontré de ce genre en utilisant à la fois les tables partitionnées et les index bitmap, mais ici on passe dans les deux cas par le même chemin pour accéder aux données.

Je ne peux que te conseiller de contacter le support Oracle.
31/03/2010, 16h47
jkofr

STAR_TRANSFORMATION_ENABLED doit être a FALSE.

Petite explication:

A mon avis, la sous requête est traitée en premier et la clause where ne peut être correctement évaluée.

jko