Jointure avec FULL ACCESS malgré table indexée

Version imprimable

Bonjour,

Suis nouveau. Pas pu trouver de réponse dans une discussion existante.

Je ne comprends pas pourquoi l'Explain Plan utilise un FULL ACCESS sur la table generalite dans cetet requette

Code:

1
2
3
4
5
Select * from
  generalite c
  join payeur p on (p.code_payeur=c.code_payeur)
where
  p.code_mode_reglement = 'CA'

Ces tables ont pas mal de lignes. Sur une requette plus complexe, c'est sensible.

Je souhaiterais un accès de ce type :
1 FULL ACCESS sur table payeur (pas le choix, code_mode_reglement n'est pas dans un index)
2 Acces index GENERALITE_INDX4 sur table generalite

Quelle syntaxe SQL utiliser ?

Explain Plan

Code:

1
2
3
4
5
Plan
SELECT STATEMENT  ALL_ROWSCost: 22 363  Bytes: 54 558 438  Cardinality: 64 262  		
	3 HASH JOIN  Cost: 22 363  Bytes: 54 558 438  Cardinality: 64 262  	
		1 TABLE ACCESS FULL TABLE BSP.PAYEUR [Analyzed]Cost: 6 358  Bytes: 12 304 092  Cardinality: 25 958  
		2 TABLE ACCESS FULL TABLE BSP.GENERALITE [Analyzed]Cost: 6 174  Bytes: 189 009 000  Cardinality: 504 024

Définition tables (simplifiée)

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
CREATE TABLE GENERALITE
(
  CODE_CLIENT           NUMBER(8)               NOT NULL,
  CODE_PAYEUR           NUMBER(8),
)
 
CREATE UNIQUE INDEX GENERALITE_PK ON GENERALITE (CODE_CLIENT);
CREATE INDEX GENERALITE_INDX4 ON GENERALITE(CODE_PAYEUR)
 
ALTER TABLE GENERALITE ADD (CONSTRAINT GENERALITE_PK PRIMARY KEY (CODE_CLIENT) USING INDEX 
 
CREATE TABLE PAYEUR
(
  CODE_CLIENT             NUMBER(8)             NOT NULL,
  CODE_PAYEUR             NUMBER(8)             NOT NULL,
  CODE_MODE_REGLEMENT     CHAR(4 BYTE),
)
 
CREATE UNIQUE INDEX PAYEUR_PK ON PAYEUR(CODE_CLIENT, CODE_PAYEUR)
CREATE UNIQUE INDEX PAYEUR_INDX6 ON PAYEUR(CODE_PAYEUR)
 
ALTER TABLE PAYEUR ADD ( CONSTRAINT PAYEUR_PK PRIMARY KEY(CODE_CLIENT, CODE_PAYEUR) USING INDEX
ALTER TABLE PAYEUR ADD ( CONSTRAINT PAYEUR_INDX6 UNIQUE (CODE_PAYEUR) USING INDEX
 
ALTER TABLE PAYEUR ADD ( CONSTRAINT PAYEUR_FK2 FOREIGN KEY (CODE_MODE_REGLEMENT) REFERENCES MODE_REGLEMENT (CODE_MODE_REGLEMENT));

28/03/2014, 16h24
mnitu

Vous pouvez forcer le plan d’exécution avec des hints SQL mais en principe le plan proposé est bon.
31/03/2014, 11h23
william44

Merci pour la réponse

Je ne connais pas le hints Oracle. Je regarde
31/03/2014, 14h36
danvoiculet

La colonne generalite.code_payeur n'est pas unique contrairement à colonne payeur.code_payeur.

S'il était une telle unicité sur la colonne je crois que Oracle n'appelle pas à une 'full scan'.
31/03/2014, 16h55
mnitu

Citation:

Envoyé par danvoiculet

La colonne generalite.code_payeur n'est pas unique contrairement à colonne payeur.code_payeur.

S'il était une telle unicité sur la colonne je crois que Oracle n'appelle pas à une 'full scan'.

C'est légèrement imprécis ce que je vais dire, mais bref : un hash join n'utilise pas les indexes pour faire la jointure.

1 pièce(s) jointe(s)

Citation:

Envoyé par mnitu

C'est légèrement imprécis ce que je vais dire, mais bref : un hash join n'utilise pas les indexes pour faire la jointure.

Oui effectivement c'est imprécis.

dans une opération HASH JOIN, il est impossible pour la build table (première table) de faire un accès sur la deuxième table (probe table) en utilisant un index sur la colonne de jointure.

SAUF si dans la requête, en plus de la condition de jointure, il y a une restriction supplémentaire sur une constante. Cette constante pouvant être transmise en dur ou en 'bind variable'.

Voici un exemple

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
 
SQL> -- j'obtiens ce que je veux uniquement avec le hint
SQL> -- use_hash. Pas besoin de no_swap_join_inputs dans ce cas
SQL> select /*+ use_hash(t1 t2)*/ t1.id1, t1.small_vc, t2.padding
  2      from t1, t2
  3      where
  4          t1.id1 = t2.id
  5      ;
 
10000 rows selected.
 
SQL_ID  g0sdqx5h5hzv0, child number 0
-------------------------------------
select /*+ use_hash(t1 t2)*/ t1.id1, t1.small_vc, t2.padding     from
t1, t2     where         t1.id1 = t2.id
 
Plan hash value: 1838229974
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   411 (100)|          |
|*  1 |  HASH JOIN         |      | 10000 |   878K|   411   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   146K|   370   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |   732K|    40   (3)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."ID1"="T2"."ID")

J'ai un index sur la table t2 sur la colonne de jointure id. Mais il n'est pas utilisé car il est impossible dans une opération HASH JOIN d'accéder à la deuxième table t2 en utilisant un index.

Mais si je change légèrement la requête en y introduisant une restriction supplémentaire

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
 
SQL> -- j'obtiens ce que je veux uniquement avec le hint
SQL> -- use_hash. Pas besoin de no_swap_join_inputs dans ce cas
SQL> select /*+ use_hash(t1 t2)*/ t1.id1, t1.small_vc, t2.padding
  2      from t1, t2
  3      where
  4          t1.id1 = t2.id
  5      and t2.id= 9610
  6      ;
 
SQL_ID  d7u9tk85kxybc, child number 0
-------------------------------------
select /*+ use_hash(t1 t2)*/ t1.id1, t1.small_vc, t2.padding     from
t1, t2     where         t1.id1 = t2.id     and t2.id= 9610
 
Plan hash value: 2749539258
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     5 (100)|          |
|*  1 |  HASH JOIN                   |         |     1 |    90 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1      |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T1_PK   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2      |     1 |    75 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2_I_FK |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="T2"."ID")
   3 - access("T1"."ID1"=9610)
   5 - access("T2"."ID"=9610)

L'exemple donné avec la question d'origine montre bien:

(1) qu'il y a une restriction supplémentaire
(2) qu'il y a un index sur la colonne représentant la restriction supplémentaire
(3) que la probe table (deuxième table) est bien la table qui possède cet index

La seule différence avec mon exemple c'est que la restriction s'applique à la colonne de jointure dans mon cas. Mais il me semble (à tester) que la clause supplémentaire ne doit pas forcément s'appliquer à la clause de jointure pour voir une opération HASH JOIN utiliser un index sur la deuxième table.

01/04/2014, 14h49
mnitu

@Mohamed
Bref, « légèrement » c’était pour dire que le hash join se fait sans utiliser l’index mais qu’il est possible que les sources des données utilisées par le hash join sont "constituées" en utilisant des indexes, ce que votre exemple démontre. Mais, je considère ça comme un détail par rapport au fond de la discussion.