Jointure et PK

Version imprimable

Jointure et PK

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:

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:

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:

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:

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

Arrrggg

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

Code:

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:

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:

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

27/05/2013, 17h05
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
27/05/2013, 20h52
pachot

Bonjour,

Citation:

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.
27/05/2013, 21h00
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?
28/05/2013, 10h05
pachot

Citation:

Envoyé par ldiaz

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.
28/05/2013, 10h36
ldiaz
3 ou 4 block pour recuperer une seule ligne via INDEX et 100 lignes par block via full access de table...

Comment puis je savoir combien de lignes seront recuperées par block avec un full access de table pour une table donnée?
Car dans beaucoup de cas je suis certain de devoir virer des indexes qui coutent plus que le full scan.

On est d'accord que tout ce que l'on a dit ici c'est dans le cas d'une requête de type:
Code:

1 2 3 select a.id, b.id from table a inner join table b on a.id=b.id;
Car si j'ai ceci:
Code:

1 2 3 4 select a.id, b.id from table a inner join table b on a.id=b.id where a.id=xx;
C'est clair que l'index sur a.id aide n'est ce pas
28/05/2013, 12h15
pachot

Citation:

Envoyé par ldiaz

On est d'accord que tout ce que l'on a dit ici c'est dans le cas d'une requête...
... C'est clair que l'index sur a.id aide n'est ce pas

Oui, bien sûr.

Citation:

Comment puis je savoir combien de lignes seront recuperées par block avec un full access de table pour une table donnée?

En regardant les statistiques dans DBA_TABLES ( NUM_ROWS / BLOCKS ) ou simplement en faisant le test:
set autotrace trace stat
select * from ...;
-> consistent gets est le bombe de blocks lus, rows processed le nombre de ligne.

Et au niveau des i/o physiques, Oracle va lire plusieurs blocks (db_file_multiblock_read_count) -> on peut souvent ramener des millerss de lignes en un seul i/o...

Citation:

Car dans beaucoup de cas je suis certain de devoir virer des indexes qui coutent plus que le full scan.

De manière très simpliste:

-> accés à peu de lignes de la table (je ne vais pas donner de chiffres, mais peu, c'est au maximum ce qui loge à l'écran ou sur une page A4 par exemple)
=> accès par index

-> accès à presque toute la table (la moité des lignes dispersées dans tous les blocs, ça peut être considéré comme un accès à toute la table)
=> full table scan

-> et entre les deux, c'est qu'aucun accès n'est optimal. Il faut revoir le design physique pour soit améliorer le full scan (en partitionnant par exemple) ou améliorer l'accès par index (en rajoutant toutes les colonnes nécessaires dans l'index pour ne pas aller voir les lignes dans la table)