Plan d'exécution différent IN ou NOT IN

**ldiaz** · 15/04/2013, 15h41

Salut a tous,

alors voici les dernieres infos:

Question histogram, j'ai executer cette requete:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT TABLE_NAME, COLUMN_NAME,HISTOGRAM, NUM_BUCKETS,LAST_ANALYZED  FROM DBA_TAB_COL_STATISTICS WHERE OWNER='SCHEMA' and table_name='ARTICULOS_ALMACEN';
ARTICULOS_ALMACEN              ID                             HEIGHT BALANCED         254 15/04/13
ARTICULOS_ALMACEN              ALMACEN                        FREQUENCY                 5 15/04/13
ARTICULOS_ALMACEN              FECHA_BAJA                     HEIGHT BALANCED         254 15/04/13
ARTICULOS_ALMACEN              STOCK_REAL                     FREQUENCY               253 15/04/13
ARTICULOS_ALMACEN              STOCK_MINIMO                   NONE                      1 15/04/13
ARTICULOS_ALMACEN              STOCK_IDEAL                    NONE                      1 15/04/13
ARTICULOS_ALMACEN              SYS_NC00007$                   HEIGHT BALANCED         254 15/04/13
ARTICULOS_ALMACEN              SYS_NC00008$                   FREQUENCY                 9 15/04/13

Les stats je les est calculées avec cette commande:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC DBMS_STATS.gather_table_stats('MNGBD', 'ARTICULOS_ALMACEN', estimate_percent => 15, cascade => TRUE);

J'ai visité ce site pour avoir un exemple:
https://blogs.oracle.com/optimizer/e..._the_optimizer

J'ai passé le parametre a 4 et j'ai executer de nouveau la requete:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
Plan hash value: 2765320330
 
----------------------------------------------------------------------------------------------------------------------------------
---------------------------------
 
| Id  | Operation                 | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OM
em |  1Mem | Used-Mem | Used-Tmp|
 
----------------------------------------------------------------------------------------------------------------------------------
---------------------------------
 
|   0 | SELECT STATEMENT          |                      |      1 |        |  23982 |00:00:15.88 |   19825 |  13407 |    124 |
   |       |          |         |
 
|   1 |  HASH GROUP BY            |                      |      1 |    936 |  23982 |00:00:15.88 |   19825 |  13407 |    124 |  44
58K|  1903K| 4300K (1)|    1024 |
 
|*  2 |   FILTER                  |                      |      1 |        |  91542 |00:00:15.67 |   19825 |  13283 |      0 |
   |       |          |         |
 
|*  3 |    HASH JOIN              |                      |      1 |   7835 |    145K|00:00:15.34 |   14675 |  13278 |      0 |  14
23K|  1198K| 1966K (0)|         |
 
|*  4 |     HASH JOIN RIGHT OUTER |                      |      1 |    914 |  12381 |00:00:13.05 |    8874 |   7478 |      0 |  11
34K|  1134K| 1326K (0)|         |
 
|*  5 |      TABLE ACCESS FULL    | PRODUCTO_PRIORIDAD   |      1 |   1376 |   1376 |00:00:00.15 |    5 |         5 |      0 |
   |       |          |         |
 
|*  6 |      HASH JOIN            |                      |      1 |    914 |  12381 |00:00:12.86 |    8869 |   7473 |      0 |  13
72K|  1219K| 1986K (0)|         |
 
|*  7 |       HASH JOIN           |                      |      1 |   1057 |  12381 |00:00:06.64 |    8142 |   7261 |      0 |  11
80K|  1180K| 1280K (0)|         |
 
|*  8 |        TABLE ACCESS FULL  | FAMILIA_NOMBRES      |      1 |    577 |    577 |00:00:00.05 |    6 |         6 |      0 |
   |       |          |         |
 
|*  9 |        HASH JOIN          |                      |      1 |   2376 |  12381 |00:00:06.53 |    8136 |   7255 |      0 |  14
15K|  1192K| 1997K (0)|         |
 
|* 10 |         HASH JOIN         |                      |      1 |   2387 |  12381 |00:00:06.24 |    7363 |   7255 |      0 |  13
23K|  1323K| 1763K (0)|         |
 
|* 11 |          TABLE ACCESS FULL| ARTICULOS_ALMACEN    |      1 |   2387 |  12381 |00:00:00.66 |    3454 |   3394 |      0 |
   |       |          |         |
 
|* 12 |          TABLE ACCESS FULL| ARTICULOS            |      1 |    346K|    346K|00:00:04.60 |    3909 |   3861 |      0 |
   |       |          |         |
 
|* 13 |         TABLE ACCESS FULL | PRODUCTOS            |      1 |  62577 |  64182 |00:00:00.07 |     773 |      0 |      0 |
   |       |          |         |
 
|* 14 |       TABLE ACCESS FULL   | PRODUCTO_ALMACEN     |      1 |  54125 |  55848 |00:00:06.02 |     727 |    212 |      0 |
   |       |          |         |
 
|* 15 |     TABLE ACCESS FULL     | NOMBRES              |      1 |    536K|    536K|00:00:00.70 |    5801 |   5800 |      0 |
   |       |          |         |
 
|* 16 |    INDEX UNIQUE SCAN      | PK_ALMACENES_BLOQUES |     18 |      1 |      6 |00:00:00.01 |   18 |         1 |      0 |
   |       |          |         |
 
|* 17 |    INDEX UNIQUE SCAN      | PK_AVANCE_BLOQUE     |   4939 |      1 |     13 |00:00:00.05 |    5132 |      4 |      0 |
   |       |          |         |
 
----------------------------------------------------------------------------------------------------------------------------------
---------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("A"."PRODUCTO" LIKE '9%' OR  IS NOT NULL OR  IS NOT NULL))
   3 - access("P"."ID"="N"."PRODUCTO")
   4 - access("PRPR"."PRODUCTO"="P"."ID")
   5 - filter(("PRPR"."PRODUCTO"<>'53006262' AND "PRPR"."PRODUCTO"<>'53328461' AND "PRPR"."PRODUCTO"<>'53328462'))
   6 - access("P"."ID"="PA"."PRODUCTO")
   7 - access("FN"."ID"=DECODE("P"."ID",'06570074','770',DECODE("P"."ID",'73268025','229',DECODE("P"."ID",'63210660','208',DECODE(
"P"."ID",'61154352','150'
 
              ,DECODE("P"."ID",'06553572','753',DECODE("P"."ID",'06563389','763',DECODE(SUBSTR("P"."ID",3,1),'9',TO_CHAR("P"."FAMI
LIA")||SUBSTR("P"."ID",4,2),'8',TO_CHAR
 
              ("P"."FAMILIA")||SUBSTR("P"."ID",4,2),'7',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",4,2),SUBSTR("P"."ID",3,3)))))))) A
ND "FN"."GENERO"="P"."GENERO")
 
   8 - filter("FN"."GENERO" IS NOT NULL)
   9 - access("P"."ID"=SUBSTR("A"."ID",0,8))
  10 - access("AA"."ID"="A"."ID")
  11 - filter(("AA"."FECHA_BAJA" IS NULL AND "AA"."ALMACEN"='001' AND TO_NUMBER(TO_CHAR("AA"."STOCK_REAL"))>0))
  12 - filter((SUBSTR("A"."ID",0,8)<>'53006262' AND SUBSTR("A"."ID",0,8)<>'53328461' AND SUBSTR("A"."ID",0,8)<>'53328462'))
  13 - filter(("P"."ID" NOT LIKE '00%' AND "P"."ID"<>'53006262' AND "P"."ID"<>'53328461' AND "P"."ID"<>'53328462'))
  14 - filter(("PA"."ALMACEN"='001' AND "PA"."PRODUCTO"<>'53006262' AND "PA"."PRODUCTO"<>'53328461' AND "PA"."PRODUCTO"<>'53328462
'))
 
  15 - filter(("N"."PRODUCTO"<>'53006262' AND "N"."PRODUCTO"<>'53328461' AND "N"."PRODUCTO"<>'53328462'))
  16 - access("ALMACEN"='001' AND "BLOQUE"=:B1)
  17 - access("ID"=:B1 AND "ALMACEN"='001')
 
EstadÝsticas
----------------------------------------------------------
         14  recursive calls
          8  db block gets
      20119  consistent gets
       3518  physical reads
          0  redo size
    2673287  bytes sent via SQL*Net to client
      18101  bytes received via SQL*Net from client
       1600  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      23982  rows processed

**ldiaz** · 15/04/2013, 16h17

Marius,
tu disais ceci:

soit à des distributions non homogène des valeurs pour les colonnes qui constituent des filtres

Tu penses que la difference entre les rows que "voit" l'optimizeur et la realité est due a que les données ne sont pas sequentielles ? y'a fragmentation?
J'ai creer une table AA_ALMACEN (CTAS) et je cree le même index (PK avec ID et ALMACEN)...
Et voici le resultat:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
Plan de Ejecuci¾n
----------------------------------------------------------
Plan hash value: 1476244927
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                      |  2995 |   503K|       |  5178   (1)| 00:01:03 |
|   1 |  HASH GROUP BY            |                      |  2995 |   503K|  4568K|  5178   (1)| 00:01:03 |
|*  2 |   FILTER                  |                      |       |       |       |            |       |
|*  3 |    HASH JOIN              |                      | 25061 |  4209K|       |  4227   (1)| 00:00:51 |
|*  4 |     HASH JOIN RIGHT OUTER |                      |  2924 |   305K|       |  2618   (2)| 00:00:32 |
|*  5 |      TABLE ACCESS FULL    | PRODUCTO_PRIORIDAD   |  1376 | 17888 |       |     4   (0)| 00:00:01 |
|*  6 |      HASH JOIN            |                      |  2924 |   268K|       |  2613   (1)| 00:00:32 |
|*  7 |       HASH JOIN           |                      |  3381 |   267K|       |  2406   (1)| 00:00:29 |
|*  8 |        TABLE ACCESS FULL  | FAMILIA_NOMBRES      |   577 |  5193 |       |     3   (0)| 00:00:01 |
|*  9 |        HASH JOIN          |                      |  7600 |   534K|       |  2403   (1)| 00:00:29 |
|* 10 |         HASH JOIN         |                      |  7634 |   395K|       |  2193   (1)| 00:00:27 |
|* 11 |          TABLE ACCESS FULL| AA_ALMACEN           |  7634 |   208K|       |   919   (1)| 00:00:12 |
|* 12 |          TABLE ACCESS FULL| ARTICULOS            |   346K|  8455K|       |  1272   (1)| 00:00:16 |
|* 13 |         TABLE ACCESS FULL | PRODUCTOS            | 62577 |  1161K|       |   208   (1)| 00:00:03 |
|* 14 |       TABLE ACCESS FULL   | PRODUCTO_ALMACEN     | 54125 |   687K|       |   206   (1)| 00:00:03 |
|* 15 |     TABLE ACCESS FULL     | NOMBRES              |   536K|    33M|       |  1606   (1)| 00:00:20 |
|* 16 |    INDEX UNIQUE SCAN      | PK_ALMACENES_BLOQUES |     1 |     7 |       |     0   (0)| 00:00:01 |
|* 17 |    INDEX UNIQUE SCAN      | PK_AVANCE_BLOQUE     |     1 |    13 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"."PRODUCTO" LIKE '9%' OR  EXISTS (SELECT 0 FROM "ALMACENES_BLOQUES"
              "ALMACENES_BLOQUES" WHERE "BLOQUE"=:B1 AND "ALMACEN"='001') OR  EXISTS (SELECT 0 FROM
              "AVANCE_BLOQUE" "AVANCE_BLOQUE" WHERE "ALMACEN"='001' AND "ID"=:B2))
   3 - access("P"."ID"="N"."PRODUCTO")
   4 - access("PRPR"."PRODUCTO"(+)="P"."ID")
   5 - filter("PRPR"."PRODUCTO"(+)<>'53006262' AND "PRPR"."PRODUCTO"(+)<>'53328461' AND
              "PRPR"."PRODUCTO"(+)<>'53328462')
   6 - access("P"."ID"="PA"."PRODUCTO")
   7 - access("FN"."ID"=DECODE("P"."ID",'06570074','770',DECODE("P"."ID",'73268025','229',DECODE("
              P"."ID",'63210660','208',DECODE("P"."ID",'61154352','150',DECODE("P"."ID",'06553572','753',DECODE(
              "P"."ID",'06563389','763',DECODE(SUBSTR("P"."ID",3,1),'9',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",
              4,2),'8',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",4,2),'7',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",
              4,2),SUBSTR("P"."ID",3,3)))))))) AND "FN"."GENERO"="P"."GENERO")
   8 - filter("FN"."GENERO" IS NOT NULL)
   9 - access("P"."ID"=SUBSTR("A"."ID",0,8))
  10 - access("AA"."ID"="A"."ID")
  11 - filter("AA"."FECHA_BAJA" IS NULL AND "AA"."ALMACEN"='001' AND
              TO_NUMBER(TO_CHAR("AA"."STOCK_REAL"))>0)
  12 - filter(SUBSTR("A"."ID",0,8)<>'53006262' AND SUBSTR("A"."ID",0,8)<>'53328461' AND
              SUBSTR("A"."ID",0,8)<>'53328462')
  13 - filter("P"."ID" NOT LIKE '00%' AND "P"."ID"<>'53006262' AND "P"."ID"<>'53328461' AND
              "P"."ID"<>'53328462')
  14 - filter("PA"."ALMACEN"='001' AND "PA"."PRODUCTO"<>'53006262' AND
              "PA"."PRODUCTO"<>'53328461' AND "PA"."PRODUCTO"<>'53328462')
  15 - filter("N"."PRODUCTO"<>'53006262' AND "N"."PRODUCTO"<>'53328461' AND
              "N"."PRODUCTO"<>'53328462')
  16 - access("ALMACEN"='001' AND "BLOQUE"=:B1)
  17 - access("ID"=:B1 AND "ALMACEN"='001')
 
Note
-----
   - dynamic sampling used for this statement (level=4)
 
 
EstadÝsticas
----------------------------------------------------------
         14  recursive calls
          5  db block gets
      19855  consistent gets
          0  physical reads
          0  redo size
    2660430  bytes sent via SQL*Net to client
      18101  bytes received via SQL*Net from client
       1600  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      23982  rows processed

**pachot** · 15/04/2013, 16h30

Au fait,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
|   A-Time   |
|00:00:04.07 |

Ca pose un problème moins de 5 secondes pour lire des millions d'enregistrements ?

**ldiaz** · 15/04/2013, 16h35

Salut
ben, vue comme une single query ça poserait pas de problemes, mais vue qu'il s'agit d'une requete qui se repete dans une procedure... Et qui sert a faire un insert, vue que l'insert depend aussi de la qualité des disques (la on a moins de champs de manoeuvre).

De plus, les previsions sont que la base va * 3 son volume, donc si les requetes ne sont pas a 100% bonnes ça va chauffer apres.

Je vais commencer par faire ce qu'on avait dis un peu plus haut, optimiser l'insert. Je vais calculer les temps.

Merci a tous !!

**mnitu** · 15/04/2013, 16h51

Ldiaz,

Soit une table tab qui contient parmi autres une colonne nommé mois qui contient le numéro du mois de l’année. Il y a douze valeurs possibles et si on suppose une distribution uniforme des ces valeurs on pouvait estimer pour une table de 1200 enregistrements que 100 vont correspondre à une recherche faite pour le mois de décembre. Mais si la réalité du busines modelé par cette table fait que pour le mois de décembre 80% des enregistrements s’y retrouvent, notre estimation initiale est erronée due à la distribution non-uniforme des valeurs pour cette colonne. La collecte des histogrammes permet alors à l’optimiseur de prendre en compte ces non-uniformités dans ses estimations.

**mnitu** · 15/04/2013, 17h00

Envoyé par ldiaz

...mais vue qu'il s'agit d'une requete qui se repete dans une procedure...

Exécuter la requête dans une boucle est très probablement une erreur d’algorithme.

**pachot** · 15/04/2013, 17h01

Envoyé par ldiaz

Salut
ben, vue comme une single query ça poserait pas de problemes, mais vue qu'il s'agit d'une requete qui se repete dans une procedure...

Alors là il y a un pb. de design.
Cette requête semble lire des ables entières. Comment se fait-il qu'on l'exécute souvent ?

**ldiaz** · 15/04/2013, 17h14

Ha d'accord, la je comprends... Ici il s'agit d'un code de magasin.
Il esiste 5 magasins:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
MAG    COUNT(1)
720     128289
075     102047
052     163072
001     103701
400     161976

le filtre va cherche le 001, donc a propos des histogram, que dois je regarder pour savoir s'ils sont correcte ou si je dois modifier quelque chose?

Voici ce que je vois dans dba_histogram:
SQL> select * from DBA_HISTOGRAMS where owner='MNGBD' and table_name='ARTICULOS_ALMACEN' and column_name='ALMACEN';

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ ------------------------------ ------------------------------ --------------- -------------- ------------------------------
BD ARTICULOS_ALMACEN ALMACEN 15793 2,5021E+35
BD ARTICULOS_ALMACEN ALMACEN 40349 2,5031E+35
BD ARTICULOS_ALMACEN ALMACEN 55756 2,5035E+35
BD ARTICULOS_ALMACEN ALMACEN 80274 2,7098E+35
BD ARTICULOS_ALMACEN ALMACEN 99776 2,8659E+35

**ldiaz** · 15/04/2013, 17h15

Oui,
toutes les heures le procedure s'execute...

**ldiaz** · 15/04/2013, 17h29

Tu sais Pachot,
c'est souvent ce qui se passe quand t'arrive dans un taf nouveau, tu recupere des requetes pas possible.

Je vais me centrer sur l'optimisation de l'insert vue que le procedure sert a ça, et ensuite je travaillerais a repenser le workflow complet.

**ldiaz** · 15/04/2013, 17h46

D'un autre coté j'etais en train de penser que les tables avec une faibles quantite de rows (si l'on considere que 650 000 rows c'est pas beaucoup).
Que pensez-vous d'utiliser KEEP_POOL pour ces quelques tables qui produirons toujours des full scan.
Il y a un autre exemple dans cette même requête:
FAMILIA_NOMBRES

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL> select GENERO, count(1) from FAMILIA_NOMBRES group by GENERO;
 
G   COUNT(1)
- ----------
         177
H        110
M        267
T        200

Jajaja
H => homme
M => mujer (donc femme)
T je seche et blanc....alors la je seche mais bon admettons.
Si je place cette table dans la Keep, celle ci et d'autre du même type je gagne en performences ?

**ldiaz** · 15/04/2013, 20h16

Re boujour,

j'ai pas vraiment de connaissance des histograms, cette video est ok pour demarrer?
https://www.youtube.com/watch?v=yPasotio22Q

quelque'un peu me donner une petite explication avec un exemple?

D'avance merci

**skuatamad** · 16/04/2013, 11h51

Comme PRODUCTO_ALMACEN n'est pas utilisée dans le SELECT il serait intéressant de la descendre dans un EXIST ou un IN.
Avec en plus un index sur (ALMACEN,PRODUCTO) qui doit être compressable, l'index est interessant même si la modification de la requête n'est pas possible.

De même pour la table ARTICULOS_ALMACEN qui n'est pas utilisé dans le SELECT (=> IN ou EXISTS).
ALMACEN n'est pas suffisament discriminent (par contre la répartition des données par magasins est homogène).
Le filtre effectué lui semble discriminent donc peut être un index sur (ALMACEN,TO_NUMBER(AA.STOCK_REAL),FECHA_BAJA,ID)
L'objectif est de pouvoir filtrer plus tôt dans le plan l'accès à ARTICULOS.

Des UNION ALL à la place des OR peuvent également être mieux, je réécrirais la requette comme ça pour tester les UNION ALL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  with aa as (
select ID 
  from ARTICULOS_ALMACEN
 where ALMACEN = '001' 
   AND TO_NUMBER(STOCK_REAL) > 0 
   AND FECHA_BAJA IS NULL
),
  a_aa as (
select id, bloque
  from ARTICULOS
 where ID in (select id from aa)
),
     a as (
select id, bloque
  from a_aa
 where BLOQUE IN (SELECT bloque FROM ALMACENES_BLOQUES WHERE almacen = '001')
 union all
select id, bloque
  from a_aa
 where PRODUCTO LIKE '9%'
 union all
select id, bloque
  from a_aa
 where PRODUCTO IN (SELECT id FROM AVANCE_BLOQUE WHERE almacen = '001')
)
SELECT P.ID,
       P.ESTILO,
       FN.GRUPO,
       P.PVP PVP,
       CALCULO_REBAJA(P.ID) REBAJA,
       N.DESCRIPCION,
       N.IDIOMA,
       SYSDATE,
       NULL, MAX(A.BLOQUE),
       MIN(PRPR.PRIORIDAD),
       N.DESCRIPCION_CORTA
  FROM PRODUCTOS P
 INNER JOIN FAMILIA_NOMBRES FN      ON FN.GENERO = P.GENERO
                                   AND decode(P.ID
		                                              ,'06570074','770'
		                                              ,'73268025','229'
		                                              ,'63210660','208'
		                                              ,'61154352','150'
		                                              ,'06553572','753'
		                                              ,'06563389','763'
		                                              ,DECODE(  substr(P.ID,3,1)
				                                                       , '9' ,P.FAM||SUBSTR(P.ID,4,2)
				                                                       , '8' ,P.FAM||SUBSTR(P.ID,4,2)
				                                                       , '7' ,P.FAM||SUBSTR(P.ID,4,2)
				                                                       , SUBSTR(P.ID,3,3)  
				                                                 )
		                                          ) = FN.ID 
 INNER JOIN NOMBRES N               ON P.ID = N.PRODUCTO 
 INNER JOIN A                       ON SUBSTR(A.ID,0,8) = P.ID
  LEFT JOIN PRODUCTO_PRIORIDAD PRPR ON PRPR.PRODUCTO = P.ID
 WHERE P.ID in (select PRODUCTO from PRODUCTO_ALMACEN where ALMACEN = '001')
   AND P.ID NOT LIKE '00%'   
   AND P.ID NOT IN('53006262','53328461','53328462')
 GROUP BY P.ID, P.ESTILO, FN.GRUPO, P.PVP, N.DESCRIPCION, N.IDIOMA, N.DESCRIPCION_CORTA;

Afin de faciliter les UNION ALL des index sur ARTICULOS (bloque,id) et sur ARTICULOS (PRODUCTO,bloque,id) peuvent être interessant (ou peut être seulement le 2ème)
Evidemment tout dépend de l'utilisation de la base, la création de multiple index ralentissant les écritures.

En même temps j'ai l'impression que c'est un batch qui tourne toutes les 3 heurs est il réellement nécessaire d'essayer de gagner quelques secondes sur l'exécution de ce SELECT ?

**ldiaz** · 16/04/2013, 12h43

Bonjour,

Whaa quelle requête, j'ai des questions:

Comme PRODUCTO_ALMACEN n'est pas utilisée dans le SELECT il serait intéressant de la descendre dans un EXIST ou un IN.
Avec en plus un index sur (ALMACEN,PRODUCTO) qui doit être compressable, l'index est interessant même si la modification de la requête n'est pas possible.

Tu veux dire que aucun champs de cette table est appelé dans le SELECT? Si tel est le cas, que veux tu dire par "descendre dans un EXIST ou un IN

Et lorsque tu dis:

L'objectif est de pouvoir filtrer plus tôt dans le plan l'accès à ARTICULOS.

C'est parce que y'a un table access full sur cette table n'est ce pas? si on le fitre on alege tout le reste de la requête?
ok je crois que je comprends...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
Plan hash value: 888758230
 
--------------------------------------------------------------------------------
------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | E-Rows |
 OMem |  1Mem | Used-Mem | Used-Tmp|
 
--------------------------------------------------------------------------------
------------------------------------
 
|   0 | SELECT STATEMENT                 |                           |        |
      |       |          |         |
 
|   1 |  TEMP TABLE TRANSFORMATION       |                           |        |
      |       |          |         |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
|   2 |   LOAD AS SELECT                 |                           |        |
  523K|   523K|  523K (0)|         |
 
|   3 |    NESTED LOOPS                  |                           |        |
      |       |          |         |
 
|   4 |     NESTED LOOPS                 |                           |    500 |
      |       |          |         |
 
|*  5 |      TABLE ACCESS FULL           | ARTICULOS_ALMACEN         |    500 |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      |       |          |         |
 
|*  6 |      INDEX UNIQUE SCAN           | PK_ARTICULOS              |      1 |
      |       |          |         |
 
|   7 |     TABLE ACCESS BY INDEX ROWID  | ARTICULOS                 |      1 |
      |       |          |         |
 
|   8 |   HASH GROUP BY                  |                           |   1406 |
 5112K|  1903K| 3269K (1)|    3072 |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   9 |    NESTED LOOPS                  |                           |        |
      |       |          |         |
 
|  10 |     NESTED LOOPS                 |                           |   1406 |
      |       |          |         |
 
|  11 |      NESTED LOOPS OUTER          |                           |    160 |
      |       |          |         |
 
|  12 |       NESTED LOOPS               |                           |    160 |
      |       |          |         |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
|* 13 |        HASH JOIN                 |                           |    183 |
 1180K|  1180K| 1236K (0)|         |
 
|* 14 |         TABLE ACCESS FULL        | FAMILIA_NOMBRES           |    577 |
      |       |          |         |
 
|* 15 |         HASH JOIN                |                           |    536 |
 1114K|  1114K| 1632K (0)|         |
 
|  16 |          VIEW                    |                           |    538 |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      |       |          |         |
 
|  17 |           UNION-ALL              |                           |        |
      |       |          |         |
 
|  18 |            NESTED LOOPS          |                           |      1 |
      |       |          |         |
 
|* 19 |             VIEW                 |                           |    500 |
      |       |          |         |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  20 |              TABLE ACCESS FULL   | SYS_TEMP_0FD9D6EE9_39EF8F |    500 |
      |       |          |         |
 
|* 21 |             INDEX UNIQUE SCAN    | PK_ALMACENES_BLOQUES      |      1 |
      |       |          |         |
 
|* 22 |            VIEW                  |                           |    500 |
      |       |          |         |
 
|  23 |             TABLE ACCESS FULL    | SYS_TEMP_0FD9D6EE9_39EF8F |    500 |
      |       |          |         |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
|  24 |            NESTED LOOPS          |                           |     37 |
      |       |          |         |
 
|* 25 |             VIEW                 |                           |    500 |
      |       |          |         |
 
|  26 |              TABLE ACCESS FULL   | SYS_TEMP_0FD9D6EE9_39EF8F |    500 |
      |       |          |         |
 
|* 27 |             INDEX UNIQUE SCAN    | PK_AVANCE_BLOQUE          |      1 |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      |       |          |         |
 
|* 28 |          TABLE ACCESS FULL       | PRODUCTOS                 |  64543 |
      |       |          |         |
 
|* 29 |        INDEX UNIQUE SCAN         | PK_PRODUCTO_ALMACEN       |      1 |
      |       |          |         |
 
|  30 |       TABLE ACCESS BY INDEX ROWID| PRODUCTO_PRIORIDAD        |      1 |
      |       |          |         |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 31 |        INDEX UNIQUE SCAN         | PK_PRODUCTO_PRIORIDAD     |      1 |
      |       |          |         |
 
|* 32 |      INDEX RANGE SCAN            | PK_NOMBRES                |      9 |
      |       |          |         |
 
|  33 |     TABLE ACCESS BY INDEX ROWID  | NOMBRES                   |      9 |
      |       |          |         |
 
--------------------------------------------------------------------------------
------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(("FECHA_BAJA" IS NULL AND "ALMACEN"='001' AND TO_NUMBER(TO_CHAR("S
TOCK_REAL"))>0))
 
   6 - access("ID"="ID")
  13 - access("FN"."GENERO"="P"."GENERO" AND "FN"."ID"=DECODE("P"."ID",'06570074
','770','73268025','229','63
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
              210660','208','61154352','150','06553572','753','06563389','763',D
ECODE(SUBSTR("P"."ID",3,1),'9',TO_CHAR("P"
 
              ."FAMILIA")||SUBSTR("P"."ID",4,2),'8',TO_CHAR("P"."FAMILIA")||SUBS
TR("P"."ID",4,2),'7',TO_CHAR("P"."FAMILIA"
 
              )||SUBSTR("P"."ID",4,2),SUBSTR("P"."ID",3,3))))
  14 - filter("FN"."GENERO" IS NOT NULL)
  15 - access("P"."ID"=SUBSTR("A"."ID",0,8))
  19 - filter((SUBSTR("ID",0,8)<>'53006262' AND SUBSTR("ID",0,8)<>'53328461' AND
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
 
              SUBSTR("ID",0,8)<>'53328462'))
  21 - access("ALMACEN"='001' AND "BLOQUE"="BLOQUE")
  22 - filter(("ID" LIKE '9%' AND SUBSTR("ID",0,8)<>'53006262' AND SUBSTR("ID",0
,8)<>'53328461' AND
 
              SUBSTR("ID",0,8)<>'53328462'))
  25 - filter((SUBSTR("ID",0,8)<>'53006262' AND SUBSTR("ID",0,8)<>'53328461' AND
 
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
              SUBSTR("ID",0,8)<>'53328462'))
  27 - access("ID"="ID" AND "ALMACEN"='001')
  28 - filter(("P"."ID" NOT LIKE '00%' AND "P"."ID"<>'53006262' AND "P"."ID"<>'5
3328461' AND
 
              "P"."ID"<>'53328462'))
  29 - access("P"."ID"="PRODUCTO" AND "ALMACEN"='001')
       filter(("PRODUCTO"<>'53006262' AND "PRODUCTO"<>'53328461' AND "PRODUCTO"<
>'53328462'))
 
  31 - access("PRPR"."PRODUCTO"="P"."ID")
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       filter(("PRPR"."PRODUCTO"<>'53006262' AND "PRPR"."PRODUCTO"<>'53328461' A
ND
 
              "PRPR"."PRODUCTO"<>'53328462'))
  32 - access("P"."ID"="N"."PRODUCTO")
       filter(("N"."PRODUCTO"<>'53006262' AND "N"."PRODUCTO"<>'53328461' AND "N"
."PRODUCTO"<>'53328462'))
 
 
Note
-----
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - Warning: basic plan statistics not available. These are only collected when
:
 
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l

J'ai ajouté un index comme tu disais:

Le filtre effectué lui semble discriminent donc peut être un index sur (ALMACEN,TO_NUMBER(AA.STOCK_REAL),FECHA_BAJA,ID)

ça a degagé ce full scan:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

|*  5 |      TABLE ACCESS FULL           | ARTICULOS_ALMACEN         |    500

Merci pour tout !

**skuatamad** · 16/04/2013, 13h50

Tu veux dire que aucun champs de cette table est appelé dans le SELECT?

Oui

Si tel est le cas, que veux tu dire par "descendre dans un EXIST ou un IN

Le descendre dans le filtre comme j'ai fait dans la requête exemple avec les UNION ALL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE P.ID in (select PRODUCTO from PRODUCTO_ALMACEN where ALMACEN = '001')

C'est parce que y'a un table access full sur cette table n'est ce pas?

Pas que, à la base il y a :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

|* 12 |          TABLE ACCESS FULL| ARTICULOS            |      1 |    346K|    346K|00:00:04.60 |    3909 |   3861 |      0 |

Donc certes il y a un access full mais surtout il récupère 346K lignes filtrées en 2 étapes, le ACCESS de l'étape 10 et le FILTER de l'étape 2.

J'ai ajouté un index comme tu disais

As tu également rajouté l'index sur PRODUCTO_ALMACEN (ALMACEN,PRODUCTO) ?

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics NOT available. These are only collected when

Peux tu recalculer les stats notamment des index ?
Peux tu également augmenter le linesize de sqlplus avant de générer le plan pour une meilleure lisibilité.

Concernant la réécriture de la requête je le ferais en 3 étapes :
- requête telle quelle avec nouveaux index
- requête avec appel à PRODUCTO_ALMACEN et ARTICULOS_ALMACEN redescendu dans le filtre avec un IN
- test avec UNION ALL, et évidemment, if faut bien valider que la requête avec UNION ALL est équivalente en résultat à la 1ère requête.

**ldiaz** · 16/04/2013, 14h57

Salut

J'ai cet index (d'origine)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE UNIQUE INDEX MNGBD.PK_PRODUCTO_ALMACEN ON MNGBD.PRODUCTO_ALMACEN
(PRODUCTO, ALMACEN)

je le remplace par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE UNIQUE INDEX MNGBD.PK_PRODUCTO_ALMACEN ON MNGBD.PRODUCTO_ALMACEN
(ALMACEN,PRODUCTO)

dans cet ordre...

Pour calculer les stats tu recommande

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER INDEX myindexname REBUILD COMPUTE STATISTICS ;

Ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBMS_STATS.GATHER_SCHEMA_STATS

Je vais le faire avec alter rebuild pour voir...

**ldiaz** · 16/04/2013, 15h25

Alors...

les index sont en places et avec stats.

La requête originale donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
Plan hash value: 419765855
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |      1 |        |  23982 |00:00:06.24 |     213K|    837 |    255 |       |       |          |         |
|   1 |  HASH GROUP BY                       |                           |      1 |    156 |  23982 |00:00:06.24 |     213K|    837 |    255 |  5112K|  1903K| 3261K (1)|    3072 |
|*  2 |   FILTER                             |                           |      1 |        |  91542 |00:00:03.35 |     213K|    582 |      0 |       |       |          |         |
|   3 |    NESTED LOOPS                      |                           |      1 |        |    145K|00:00:04.38 |     212K|    582 |      0 |       |       |          |         |
|   4 |     NESTED LOOPS                     |                           |      1 |   1307 |    145K|00:00:02.90 |   68648 |    534 |      0 |       |       |          |         |
|   5 |      NESTED LOOPS OUTER              |                           |      1 |    148 |  12380 |00:00:02.24 |   51303 |    158 |      0 |       |       |          |         |
|   6 |       NESTED LOOPS                   |                           |      1 |    148 |  12380 |00:00:02.09 |   51281 |    155 |      0 |       |       |          |         |
|*  7 |        HASH JOIN                     |                           |      1 |    170 |  12380 |00:00:01.92 |   36539 |     93 |      0 |  1180K|  1180K| 1271K (0)|         |
|*  8 |         TABLE ACCESS FULL            | FAMILIA_NOMBRES           |      1 |    577 |    577 |00:00:00.01 |       6 |      0 |      0 |       |       |          |         |
|*  9 |         HASH JOIN                    |                           |      1 |    498 |  12381 |00:00:01.83 |   36533 |     93 |      0 |  1415K|  1192K| 1983K (0)|         |
|  10 |          NESTED LOOPS                |                           |      1 |        |  12381 |00:00:15.06 |   35760 |     93 |      0 |       |       |          |         |
|  11 |           NESTED LOOPS               |                           |      1 |    500 |  12381 |00:00:00.15 |   23379 |      0 |      0 |       |       |          |         |
|* 12 |            INDEX RANGE SCAN          | IDX_ARTICULOS_ALMACEN_STR |      1 |    500 |  12381 |00:00:00.01 |      59 |      0 |      0 |       |       |          |         |
|* 13 |            INDEX UNIQUE SCAN         | PK_ARTICULOS              |  12381 |      1 |  12381 |00:00:00.07 |   23320 |      0 |      0 |       |       |          |         |
|  14 |           TABLE ACCESS BY INDEX ROWID| ARTICULOS                 |  12381 |      1 |  12381 |00:00:01.65 |   12381 |     93 |      0 |       |       |          |         |
|* 15 |          TABLE ACCESS FULL           | PRODUCTOS                 |      1 |  64543 |  64182 |00:00:00.09 |     773 |      0 |      0 |       |       |          |         |
|* 16 |        INDEX UNIQUE SCAN             | PK_PRODUCTO_ALMACEN       |  12380 |      1 |  12380 |00:00:00.37 |   14742 |     62 |      0 |       |       |          |         |
|  17 |       TABLE ACCESS BY INDEX ROWID    | PRODUCTO_PRIORIDAD        |  12380 |      1 |      4 |00:00:00.09 |      22 |      3 |      0 |       |       |          |         |
|* 18 |        INDEX UNIQUE SCAN             | PK_PRODUCTO_PRIORIDAD     |  12380 |      1 |      4 |00:00:00.04 |      18 |      3 |      0 |       |       |          |         |
|* 19 |      INDEX RANGE SCAN                | PK_NOMBRES                |  12380 |      9 |    145K|00:00:01.85 |   17345 |    376 |      0 |       |       |          |         |
|  20 |     TABLE ACCESS BY INDEX ROWID      | NOMBRES                   |    145K|      9 |    145K|00:00:01.21 |     143K|     48 |      0 |       |       |          |         |
|* 21 |    INDEX UNIQUE SCAN                 | PK_ALMACENES_BLOQUES      |     18 |      1 |      6 |00:00:00.01 |      18 |      0 |      0 |       |       |          |         |
|* 22 |    INDEX UNIQUE SCAN                 | PK_AVANCE_BLOQUE          |   1107 |      1 |      3 |00:00:00.01 |    1411 |      0 |      0 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("A"."PRODUCTO" LIKE '9%' OR  IS NOT NULL OR  IS NOT NULL))
   7 - access("FN"."ID"=DECODE("P"."ID",'73268025','229',DECODE("P"."ID",'63210660','208',DECODE("P"."ID",'61154352','150',DECODE("P"."ID",'06553572','753',DECODE("P"."ID"
              ,'06563389','763',DECODE(SUBSTR("P"."ID",3,1),'9',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",4,2),'8',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",4,2),'7',TO_CHAR("P"."FAMIL
IA")|
 
              |SUBSTR("P"."ID",4,2),SUBSTR("P"."ID",3,3))))))) AND "FN"."GENERO"="P"."GENERO")
   8 - filter("FN"."GENERO" IS NOT NULL)
   9 - access("P"."ID"=SUBSTR("A"."ID",0,8))
  12 - access("AA"."ALMACEN"='001' AND "AA"."SYS_NC00007$">0 AND "AA"."FECHA_BAJA" IS NULL AND "AA"."SYS_NC00007$" IS NOT NULL)
       filter("AA"."FECHA_BAJA" IS NULL)
  13 - access("AA"."ID"="A"."ID")
       filter((SUBSTR("A"."ID",0,8)<>'53006262' AND SUBSTR("A"."ID",0,8)<>'53328461' AND SUBSTR("A"."ID",0,8)<>'53328462'))
  15 - filter(("P"."ID" NOT LIKE '00%' AND "P"."ID"<>'53006262' AND "P"."ID"<>'53328461' AND "P"."ID"<>'53328462'))
  16 - access("PA"."ALMACEN"='001' AND "P"."ID"="PA"."PRODUCTO")
       filter(("PA"."PRODUCTO"<>'53006262' AND "PA"."PRODUCTO"<>'53328461' AND "PA"."PRODUCTO"<>'53328462'))
  18 - access("PRPR"."PRODUCTO"="P"."ID")
       filter(("PRPR"."PRODUCTO"<>'53006262' AND "PRPR"."PRODUCTO"<>'53328461' AND "PRPR"."PRODUCTO"<>'53328462'))
  19 - access("P"."ID"="N"."PRODUCTO")
       filter(("N"."PRODUCTO"<>'53006262' AND "N"."PRODUCTO"<>'53328461' AND "N"."PRODUCTO"<>'53328462'))
  21 - access("ALMACEN"='001' AND "BLOQUE"=:B1)
  22 - access("ID"=:B1 AND "ALMACEN"='001')
 
EstadÝsticas
----------------------------------------------------------
          4  recursive calls
          1  db block gets
     213556  consistent gets
        255  physical reads
          0  redo size
    2626071  bytes sent via SQL*Net to client
      18102  bytes received via SQL*Net from client
       1600  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      23982  rows processed

La nouvelle requête donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Plan hash value: 2659730148
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |        |       |       |       |    |
|   1 |  TEMP TABLE TRANSFORMATION       |                           |        |       |       |       |    |
|   2 |   LOAD AS SELECT                 |                           |        |   523K|   523K|  523K (0)|         |
|   3 |    NESTED LOOPS                  |                           |        |       |       |       |    |
|   4 |     NESTED LOOPS                 |                           |    500 |       |       |       |    |
|*  5 |      INDEX RANGE SCAN            | IDX_ARTICULOS_ALMACEN_STR |    500 |       |       |       |    |
|*  6 |      INDEX UNIQUE SCAN           | PK_ARTICULOS              |      1 |       |       |       |    |
|   7 |     TABLE ACCESS BY INDEX ROWID  | ARTICULOS                 |      1 |       |       |       |    |
|   8 |   HASH GROUP BY                  |                           |   1406 |  4775K|  1911K| 3257K (1)|    2048 |
|   9 |    NESTED LOOPS                  |                           |        |       |       |       |    |
|  10 |     NESTED LOOPS                 |                           |   1406 |       |       |       |    |
|  11 |      NESTED LOOPS OUTER          |                           |    160 |       |       |       |    |
|* 12 |       HASH JOIN                  |                           |    160 |  1280K|  1078K| 1600K (0)|         |
|* 13 |        HASH JOIN                 |                           |    183 |  1180K|  1180K| 1249K (0)|         |
|* 14 |         TABLE ACCESS FULL        | FAMILIA_NOMBRES           |    577 |       |       |       |    |
|* 15 |         HASH JOIN                |                           |    536 |  1114K|  1114K| 1626K (0)|         |
|  16 |          VIEW                    |                           |    538 |       |       |       |    |
|  17 |           UNION-ALL              |                           |        |       |       |       |    |
|  18 |            NESTED LOOPS          |                           |      1 |       |       |       |    |
|* 19 |             VIEW                 |                           |    500 |       |       |       |    |
|  20 |              TABLE ACCESS FULL   | SYS_TEMP_0FD9D6F08_39EF8F |    500 |       |       |       |    |
|* 21 |             INDEX UNIQUE SCAN    | PK_ALMACENES_BLOQUES      |      1 |       |       |       |    |
|* 22 |            VIEW                  |                           |    500 |       |       |       |    |
|  23 |             TABLE ACCESS FULL    | SYS_TEMP_0FD9D6F08_39EF8F |    500 |       |       |       |    |
|  24 |            NESTED LOOPS          |                           |     37 |       |       |       |    |
|* 25 |             VIEW                 |                           |    500 |       |       |       |    |
|  26 |              TABLE ACCESS FULL   | SYS_TEMP_0FD9D6F08_39EF8F |    500 |       |       |       |    |
|* 27 |             INDEX UNIQUE SCAN    | PK_AVANCE_BLOQUE          |      1 |       |       |       |    |
|* 28 |          TABLE ACCESS FULL       | PRODUCTOS                 |  64543 |       |       |       |    |
|* 29 |        INDEX FAST FULL SCAN      | PK_PRODUCTO_ALMACEN       |  56293 |       |       |       |    |
|  30 |       TABLE ACCESS BY INDEX ROWID| PRODUCTO_PRIORIDAD        |      1 |       |       |       |    |
|* 31 |        INDEX UNIQUE SCAN         | PK_PRODUCTO_PRIORIDAD     |      1 |       |       |       |    |
|* 32 |      INDEX RANGE SCAN            | PK_NOMBRES                |      9 |       |       |       |    |
|  33 |     TABLE ACCESS BY INDEX ROWID  | NOMBRES                   |      9 |       |       |       |    |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("ALMACEN"='001' AND "ARTICULOS_ALMACEN"."SYS_NC00007$">0 AND "FECHA_BAJA" IS NULL AND
              "ARTICULOS_ALMACEN"."SYS_NC00007$" IS NOT NULL)
       filter("FECHA_BAJA" IS NULL)
   6 - access("ID"="ID")
  12 - access("P"."ID"="PRODUCTO")
  13 - access("FN"."GENERO"="P"."GENERO" AND "FN"."ID"=DECODE("P"."ID",'06570074','770','73268025','229','63
              210660','208','61154352','150','06553572','753','06563389','763',DECODE(SUBSTR("P"."ID",3,1),'9',TO_CHAR("P"
              ."FAMILIA")||SUBSTR("P"."ID",4,2),'8',TO_CHAR("P"."FAMILIA")||SUBSTR("P"."ID",4,2),'7',TO_CHAR("P"."FAMILIA"
              )||SUBSTR("P"."ID",4,2),SUBSTR("P"."ID",3,3))))
  14 - filter("FN"."GENERO" IS NOT NULL)
  15 - access("P"."ID"=SUBSTR("A"."ID",0,8))
  19 - filter((SUBSTR("ID",0,8)<>'53006262' AND SUBSTR("ID",0,8)<>'53328461' AND
              SUBSTR("ID",0,8)<>'53328462'))
  21 - access("ALMACEN"='001' AND "BLOQUE"="BLOQUE")
  22 - filter(("ID" LIKE '9%' AND SUBSTR("ID",0,8)<>'53006262' AND SUBSTR("ID",0,8)<>'53328461' AND
              SUBSTR("ID",0,8)<>'53328462'))
  25 - filter((SUBSTR("ID",0,8)<>'53006262' AND SUBSTR("ID",0,8)<>'53328461' AND
              SUBSTR("ID",0,8)<>'53328462'))
  27 - access("ID"="ID" AND "ALMACEN"='001')
  28 - filter(("P"."ID" NOT LIKE '00%' AND "P"."ID"<>'53006262' AND "P"."ID"<>'53328461' AND
              "P"."ID"<>'53328462'))
  29 - filter(("ALMACEN"='001' AND "PRODUCTO"<>'53006262' AND "PRODUCTO"<>'53328461' AND
              "PRODUCTO"<>'53328462'))
  31 - access("PRPR"."PRODUCTO"="P"."ID")
       filter(("PRPR"."PRODUCTO"<>'53006262' AND "PRPR"."PRODUCTO"<>'53328461' AND
              "PRPR"."PRODUCTO"<>'53328462'))
  32 - access("P"."ID"="N"."PRODUCTO")
       filter(("N"."PRODUCTO"<>'53006262' AND "N"."PRODUCTO"<>'53328461' AND "N"."PRODUCTO"<>'53328462'))
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
EstadÝsticas
----------------------------------------------------------
         45  recursive calls
         44  db block gets
      46636  consistent gets
         36  physical reads
        620  redo size
    2658854  bytes sent via SQL*Net to client
      18091  bytes received via SQL*Net from client
       1599  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      23957  rows processed

Les Consistents Gets sont netement meilleurs avec la 2eme, les Physical Reads aussi, le seul soucis c'est comme tu disais, le resultat est un peu different.
1er: 23982 rows processed
2eme: 23957 rows processed

25 lignes ..... la poisse non?

**skuatamad** · 16/04/2013, 16h57

Envoyé par ldiaz

1er: 23982 rows processed
2eme: 23957 rows processed

25 lignes ..... la poisse non?

Je ne vois pas trop...
J'avais oublié de sélectionner PRODUCTO dans a_aa mais à priori tu l'as rajouté de toi même, sinon essaie en remettant les DECODE imbriqués de la requête de base et pas la version simplifiée au cas où...

**ldiaz** · 16/04/2013, 17h34

Et bien je vois pas non plus... La difference doit venir des union...
Car le reste semble etre identique.

Comment serait la même requete sams les Union?

**skuatamad** · 16/04/2013, 17h48

Ben les UNION ALL servent à éliminer les OR, donc sans les UNION ALL il faut faire des OR :
Que donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT P.ID,
       P.ESTILO,
       FN.GRUPO,
       P.PVP PVP,
       CALCULO_REBAJA(P.ID) REBAJA,
       N.DESCRIPCION,
       N.IDIOMA,
       SYSDATE,
       NULL, MAX(A.BLOQUE),
       MIN(PRPR.PRIORIDAD),
       N.DESCRIPCION_CORTA
  FROM PRODUCTOS P
 INNER JOIN FAMILIA_NOMBRES FN ON FN.GENERO=P.GENERO
 INNER JOIN NOMBRES N ON P.ID = N.PRODUCTO 
 INNER JOIN ARTICULOS A ON SUBSTR(A.ID,0,8) = P.ID 
  LEFT JOIN PRODUCTO_PRIORIDAD PRPR ON PRPR.PRODUCTO = P.ID
 WHERE (
	     A.BLOQUE IN (SELECT bloque FROM ALMACENES_BLOQUES WHERE almacen = '001')
	     OR 
	     A.PRODUCTO LIKE '9%'
		   OR 
	     A.PRODUCTO IN (SELECT id FROM AVANCE_BLOQUE WHERE almacen = '001')
       )
   AND A.ID in (select AA.ID 
                  from ARTICULOS_ALMACEN AA 
                 where AA.ALMACEN = '001' 
                   AND TO_NUMBER(AA.STOCK_REAL) > 0 
                   AND AA.FECHA_BAJA IS NULL)
   and P.ID in (select PA.PRODUCTO from PRODUCTO_ALMACEN PA where PA.ALMACEN = '001')
   AND P.ID NOT LIKE '00%' 
   AND decode(P.ID,'06570074','770', decode(P.ID,'73268025','229', decode(P.ID,'63210660','208', decode(P.ID,'61154352','150', decode(P.ID,'06553572','753', decode(P.ID,'06563389','763', DECODE(substr(P.ID,3,1), '9', P.FAMILIA||SUBSTR(P.ID,4,2), '8', P.FAMILIA||SUBSTR(P.ID,4,2), '7', P.FAMILIA||SUBSTR(P.ID,4,2), SUBSTR(P.ID,3,3) ) ) ) ) ) ) ) = FN.ID 
   AND P.ID NOT IN('53006262','53328461','53328462')
 GROUP BY P.ID, P.ESTILO, FN.GRUPO, P.PVP, N.DESCRIPCION, N.IDIOMA, N.DESCRIPCION_CORTA;

Tu avais bien essayé la requête avec UNION et le DECODE complexe ?

Plan d'exécution différent IN ou NOT IN [10g]

SQL Oracle

Discussions similaires

Partager

Partager