Discussion :

[floriann]

Bonjour,

Je m'intéresse actuellement aux plans d'exécution sous Oracle. Malheureusement, quelque chose m'échappe.

Imaginons le schéma suivant :
Table1(attribut1_pk, attribut2)
Table2(attribut3, attribut4, #attribut1_fkey)
On suppose donc avoir un index sur les clés primaires et sur l'attribut de jointure table2.attribut1_fkey.

J'exécute la requête suivante :

Code :

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

1
2
 
select attribut4 from table1 natural join table2

J'obtiens alors le plan d'exécution suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
---------------------------------------------------------------
--| Id  | Operation                    | Name         | Rows  |
---------------------------------------------------------------
--|   0 | SELECT STATEMENT             |              |    48 |
--|   1 |  MERGE JOIN                  |              |    48 |
--|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE2       |    81 |
--|   3 |    INDEX FULL SCAN           | I_TABLE2     |    81 |
--|*  4 |   SORT JOIN                  |              |    48 |
--|   5 |    INDEX FULL SCAN           | PK_TABLE     |    48 |
---------------------------------------------------------------

Le CBO choisit donc une jointure par Merge Join. Comme je récupère l'attribut 4, je ne peux pas me contenter de faire un Index Full Scan sur la table 2, comme pour la table 1. Jusque là, je comprends.

Ce qui me gêne concerne les opérations 2 et 3, c'est-à-dire l'utilisation conjointe d'un Index Full Scan et d'un Table Access By Rowid. Quel est l'intérêt de faire un Index Full Scan, qui va retourner les rowid de toutes les lignes, puis ensuite d'accéder à tous ces Rowid un par un ? Ne serait-il pas plus pratique de faire un Table Access Full directement ?

Autre hypothèse : je me trompe et les Table Access By Index Rowid ne concernent que les lignes qui auront été jointes à l'aide des deux index ? Auquel cas, ces opérations me choqueraient moins.

Merci d'avance pour les éclaircissements.

[pachot]

Bonjour,

Quel est l'intérêt de faire un Index Full Scan, qui va retourner les rowid de toutes les lignes, puis ensuite d'accéder à tous ces Rowid un par un ? Ne serait-il pas plus pratique de faire un Table Access Full directement ?

Exact. L'interêt, c'est que par cette méthode d'accès les lignes dont retournées dans l'ordre. Don pas besoin de les trier pour faire le merge join.

Autre hypothèse : je me trompe et les Table Access By Index Rowid ne concernent que les lignes qui auront été jointes à l'aide des deux index ? Auquel cas, ces opérations me choqueraient moins.

Non, la jointure se fait après, lorsqu'on accède à Table1 trié. C'est pour cela que le prédicat (noté par * et visible dans la section Predicate) est à l'opération 4.

Il y a 2 chose à comprendre dans ce plan d'exécution:

1. l'index full scan de Table1 semble retourner les lignes dans l'ordre mais Oracle fait un SORT JOIN quand même.
c'est quelque chose que j'ai déjà vu. Il ne s'agit peut-être pas d'un vrai SORT. Je testerai à l'occasion.

2. vu le nombre de lignes estimées, il semblerait moins coûteux de faire un hash join: Table 1 allant en table de hachage (ça ne devrait pas être plus coûteux de faire une table de hachage que de trier).

Pour vérifier le point 2. il faudrait forçer un hash join pour voir la différence de coût:
SELECT /*+ leading(table1 table2) use_hash(table2) */ attribut4 FROM table1 NATURAL JOIN table2

Cordialement,
Franck.

[pachot]

Je n'ai pas résisté à reproduire le test -> ci-dessous.

Chez moi, le CBO choisit par défaut un NESTED LOOP JOIN.
Et lors de l'exécution, c'est le HASH JOIN qui est le moins coûteux.
Je ne sais pas ce qui fait chez toi que MERGE JOIN est choisi. version, paramètres, statistiques...

D'autre part, j'ai tracé les SORT (event 10032) et c'est effectivement un vrai tri qui est fait. Je ne sais pas pourquoi puisqu'il paraît inutile. Mais il semble que le SORT ne peut être évité que lorsqu'il est en 1ère position du MERGE JOIN...

Le même plan d'exécution reproduit:

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
 
SQL> create table table1 (attribut1 constraint pk_table primary key) as select rownum attribut1 from dual connect by level <=48;
 
Table created.
 
SQL> create table table2 (attribut3 primary key,attribut4,attribut1) as select rownum attribut3 ,'x' attribut4,rownum attribut1 from dual connect by level <=81;
 
Table created.
 
SQL> create index i_table2 on table2(attribut1);
 
Index created.
 
SQL>
SQL> set pagesize 1000 linesize 200 autotrace trace
SQL> prompt -- avec MERGE JOIN
-- avec MERGE JOIN
SQL> SELECT /*+ leading(table2 table1) use_merge(table1) */ attribut4 FROM table1 NATURAL JOIN table2;
 
48 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2925551499
 
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    48 |   384 |     3   (0)| 00:00:01 |
|   1 |  MERGE JOIN                  |          |    48 |   384 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE2   |    81 |   405 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | I_TABLE2 |    81 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |    48 |   144 |     1   (0)| 00:00:01 |
|   5 |    INDEX FULL SCAN           | PK_TABLE |    48 |   144 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("TABLE1"."ATTRIBUT1"="TABLE2"."ATTRIBUT1")
       filter("TABLE1"."ATTRIBUT1"="TABLE2"."ATTRIBUT1")
 
 
Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         48  rows processed
 
SQL> prompt -- avec HASH JOIN
-- avec HASH JOIN
SQL> SELECT /*+ leading(table1 table2) use_hash(table2) */ attribut4 FROM table1 NATURAL JOIN table2;
 
48 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2848128795
 
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    48 |   384 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |          |    48 |   384 |     4   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_TABLE |    48 |   144 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TABLE2   |    81 |   405 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("TABLE1"."ATTRIBUT1"="TABLE2"."ATTRIBUT1")
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         48  rows processed
 
SQL> prompt -- sans hint
-- sans hint
SQL> SELECT attribut4 FROM table1 NATURAL JOIN table2;
 
48 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2815901863
 
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    48 |   384 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |          |    48 |   384 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TABLE2   |    81 |   405 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_TABLE |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("TABLE1"."ATTRIBUT1"="TABLE2"."ATTRIBUT1")
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         48  rows processed

La trace du SORT après ALTER SESSION SET EVENTS '10032 trace name context forever, level 10'; :

Code :

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

1
2
3
4
5
6
7
8
9
 
---- Sort Statistics ------------------------------
Input records                             48
Output records                            48
Total number of comparisons performed     175
  Comparisons performed by in-memory sort 175
Total amount of memory used               2048
Uses version 2 sort
---- End of Sort Statistics -----------------------

... il y a bien eu 48 lignes triées alors qu'elles étaient déjà dans l'ordre

[jkofr]

Hello,

Tu pourrai éviter le table access en incluant la colonne attribut4 dans l'index de la table1.

jko

[mnitu]

...
Et lors de l'exécution, c'est le HASH JOIN qui est le moins coûteux.
...

@pachot
Si je ne m’abuse pas c’est plus efficace en termes des opérations I/O mais pas forcement en temps total d’exécution. Et il est sans doute que le HASH JOIN n'est pas moins coûteux pour l'optimiseur.

[floriann]

Merci beaucoup, pachot, pour le retour.

Avec tes explications très détaillées, tout me paraît logique et clair.

Encore merci !