Discussion :

[ldiaz]

Bonjour a tous,
j'ai une petite question relative à une PK composée de 2 champs.

(oracle 11g sous windows)

J'ai cette requête:

Code :

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

1
2
3
select /*+ gather_plan_statistics */ p.base
from productos p
INNER JOIN producto_color_defecto pf2 ON p.base = pf2.producto ;

Cette requête donne ce plan:

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
Plan hash value: 1553037060
 
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |      1 |        |  66171 |00:00:00.31 |    4697 |    178 |       |       |          |
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |                        |      1 |  60850 |  66171 |00:00:00.31 |    4697 |    178 |  2162K|  1648K| 3131K (0)|
|   2 |   TABLE ACCESS FULL   | PRODUCTO_COLOR_DEFECTO |      1 |  38962 |  38962 |00:00:00.06 |     106 |      0 |       |       |          |
|   3 |   INDEX FAST FULL SCAN| IDX_PRODUCTOS_BASE     |      1 |  68251 |  66319 |00:00:00.24 |    4591 |    178 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("P"."BASE"="PF2"."PRODUCTO")

Petit full scan sur la table de droite (producto_color_defecto)

Le comportement "normal" pour moi ici serait utilisation des indexes et pas de full scan. Donc j'ai regardé les index et sur cette table j'ai ceci :

Code :

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

1
2
3
4
5
6
7
8
CREATE UNIQUE INDEX PK_PRODUCTO_COLOR_DEFECTO ON PRODUCTO_COLOR_DEFECTO
(PRODUCTO, COLOR)
 
ALTER TABLE PRODUCTO_COLOR_DEFECTO ADD (
  CONSTRAINT PK_PRODUCTO_COLOR_DEFECTO
  PRIMARY KEY
  (PRODUCTO, COLOR)
  USING INDEX PK_PRODUCTO_COLOR_DEFECTO);

Il existe une PK sur l'id de la table de gauche.

Je me doute que c'est a cause de la PK combinée donc je crée ceci :

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
create table prd_color_def as select * from producto_color_defecto
 
CREATE INDEX PK_PROD_COLOR_DEF ON prd_color_def(PRODUCTO)
 
BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'MONSCHEMA'
     ,TabName        => 'PRD_COLOR_DEF'
    ,Estimate_Percent  => 10
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1'
    ,Degree            => 0
    ,Cascade           => FALSE
    ,No_Invalidate     => FALSE);
END;
/

Et la j’obtiens le plan que j’imaginais. Donc c'est bien à cause de la PK combinée ?
Pourquoi Oracle choisit de faire un full scan, le premier des champs de la PK étant celui utilisé dans la requête ?

Pour éviter le full scan, faut-il ajouter un index sur le champ productos seul ?? Oracle l'utilisera dans ce cas ?

D'avance merci

[ldiaz]

Arrrggg

j'ai ajouté un index sur productos:
J'ai plus de full scan...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Plan de Ejecuci¾n
----------------------------------------------------------
Plan hash value: 340045172
 
--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    | 60206 |  1058K|    85   (3)| 00:00:02 |
|*  1 |  HASH JOIN            |                    | 60206 |  1058K|    85   (3)| 00:00:02 |
|   2 |   INDEX FAST FULL SCAN| IDX_PRD_DEF        | 38550 |   338K|    31   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| IDX_PRODUCTOS_BASE | 68251 |   599K|    52   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
EstadÝsticas
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4707  consistent gets
          0  physical reads
          0  redo size
    1300881  bytes sent via SQL*Net to client
      49044  bytes received via SQL*Net from client
       4413  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      66171  rows processed

Mais la quantité de GETS avec l'index est un peu superieur que sans:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
EstadÝsticas
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4697  consistent gets
          0  physical reads
          0  redo size
    1300881  bytes sent via SQL*Net to client
      49044  bytes received via SQL*Net from client
       4413  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      66171  rows processed

la je suis dans le cas ou un full scan index coute plus que full scan de table n'est ce pas?
Cette solution vaut sur une table beaucoup plus grande?

D'avance merci

[mnitu]

Hash join ne peut pas appliquer les conditions de jointure via les indexes comme les nested loops. Il peut les utiliser pour filtrer les données avant la jointure ou encore à la place des tables.

/Edit
Lisez aussi The most fundamental difference between hash and nested loop joins

[pachot]

Bonjour,

Le comportement "normal" pour moi ici serait utilisation des indexes et pas de full scan. Donc j'ai regardé les index et sur cette table j'ai ceci :

Pourquoi ? vous lisez toutes les lignes des 2 tables. Un full scan est le plus efficace.
Cordialement,
Franck.

[ldiaz]

Salut Pacho
oui je suis d'accord, mais j'ai lu que pour un JOIN il faut utiliser un champ de jointure qui preferablement est une PK, et d'ailleurs lorsque tu fais une requete avec INNER JOIN ON a.pk=b.pk le paquet recuperé se fait avec un cost moins elevé qu'avec des full scan. Imagine 2 tables de 4 millons de rows chaqune?

[pachot]

Imagine 2 tables de 4 millons de rows chaqune?

Justement:

Accès via index: il faut lire 3 ou 4 blocks pour accéder à la ligne. Donc si on le fait 4 millions de fois -> 12 millions de logical reads

Accès via full scan: on lit une seule fois toute la table. S'il y a en moyenne 100 lignes par bloc -> 40000 logical reads.