WITH et plan d'exécution

Version imprimable

Bonjour,
J’ai un CAS 1 (voir ci-dessous) que je pensais optimiser en écrivant la requête comme indiqué dans le CAS 2 (voir ci-dessous).Et à priori, en lisant le plan d’exécution, je crois comprendre que c’est pire. Pourriez-vous m’expliquer pourquoi ?
Note :

Le but de la discussion n’est pas de modifier la requête mais bien de comprendre pourquoi la version du CAS 2 ne semble pas, selon le plan d’exécution, plus performante.
Je n’ai pas accès aux statistiques mais bon sang de bonsoir, je pense que le «consistent gets » du CAS 2 doit être inférieur à celui du CAS 1 car ma sous-requête de la clause WITH est exécutée qu’une seule fois alors que celle du CAS 1 , 3 fois.

TABLES

Code:

1
2
CREATE TABLE T1 (T1_ID01 VARCHAR2(2),T1_ID02 VARCHAR2(2),T1_ID03 VARCHAR2(2),T1_ID04 VARCHAR2(2),T1_COL01 VARCHAR2(2),T1_COL02 VARCHAR2(2),T1_COL03 NUMBER,PRIMARY KEY (T1_ID01,T1_ID02,T1_ID03,T1_ID04));
CREATE TABLE T2 (T2_ID01 VARCHAR2(2),T2_ID02 VARCHAR2(2),T2_ID03 VARCHAR2(2),T2_COL01 VARCHAR2(2),T2_COL02 VARCHAR2(2),PRIMARY KEY (T2_ID01,T2_ID02,T2_ID03));

CAS 1

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
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
(
  (
  SELECT DISTINCT T1_ID03,T1_COL01
  FROM T1
  WHERE 
  T1_ID01='A' AND T1_ID02 = 'B' AND T1_COL02 = 'C' AND T1_COL03 > 100
  AND T1_ID03 IN (SELECT T2_ID01 FROM T2 WHERE T2_ID01='A' AND T2_ID02 = 'B')
  )
)
UNION
(
  (
  SELECT DISTINCT T1_ID03,T1_COL01
  FROM T1
  WHERE 
  T1_ID01='A' AND T1_ID02 = 'B' AND T1_COL02 = 'D' AND T1_COL03 > 252
  AND T1_ID03 IN (SELECT DISTINCT T2_ID01 FROM T2 WHERE T2_ID01='A' AND T2_ID02 = 'B')
  )
)
UNION
(
  (
  SELECT DISTINCT T1_ID03,T1_COL01
  FROM T1
  WHERE 
  T1_ID01='A' AND T1_ID02 = 'B' AND T1_COL02 = 'F' AND T1_COL03 > 586
  AND T1_ID03 IN (SELECT T2_ID01 FROM T2 WHERE T2_ID01='A' AND T2_ID02 = 'B')
  )
)
 
 
 
------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    15 |   510 |    15  (74)| 00:12:01 |
|   1 |  SORT UNIQUE                   |               |    15 |   510 |    15  (74)| 00:12:01 |
|   2 |   UNION-ALL                    |               |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN        |               |     5 |   170 |     4   (0)| 00:03:13 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T1            |     1 |    28 |     3   (0)| 00:02:25 |
|*  5 |      INDEX RANGE SCAN          | SYS_C00592542 |     1 |       |     2   (0)| 00:01:37 |
|   6 |     BUFFER SORT                |               |  1000 |  6000 |     1   (0)| 00:00:49 |
|*  7 |      INDEX RANGE SCAN          | SYS_C00592543 |  1000 |  6000 |     1   (0)| 00:00:49 |
|   8 |    MERGE JOIN CARTESIAN        |               |     5 |   170 |     4   (0)| 00:03:13 |
|*  9 |     TABLE ACCESS BY INDEX ROWID| T1            |     1 |    28 |     3   (0)| 00:02:25 |
|* 10 |      INDEX RANGE SCAN          | SYS_C00592542 |     1 |       |     2   (0)| 00:01:37 |
|  11 |     BUFFER SORT                |               |  1000 |  6000 |     1   (0)| 00:00:49 |
|* 12 |      INDEX RANGE SCAN          | SYS_C00592543 |  1000 |  6000 |     1   (0)| 00:00:49 |
|  13 |    MERGE JOIN CARTESIAN        |               |     5 |   170 |     4   (0)| 00:03:13 |
|* 14 |     TABLE ACCESS BY INDEX ROWID| T1            |     1 |    28 |     3   (0)| 00:02:25 |
|* 15 |      INDEX RANGE SCAN          | SYS_C00592542 |     1 |       |     2   (0)| 00:01:37 |
|  16 |     BUFFER SORT                |               |  1000 |  6000 |     1   (0)| 00:00:49 |
|* 17 |      INDEX RANGE SCAN          | SYS_C00592543 |  1000 |  6000 |     1   (0)| 00:00:49 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("T1_COL02"='C' AND "T1_COL03">100)
   5 - access("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_ID03"='A')
   7 - access("T2_ID01"='A' AND "T2_ID02"='B')
   9 - filter("T1_COL02"='D' AND "T1_COL03">252)
  10 - access("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_ID03"='A')
  12 - access("T2_ID01"='A' AND "T2_ID02"='B')
  14 - filter("T1_COL02"='F' AND "T1_COL03">586)
  15 - access("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_ID03"='A')
  17 - access("T2_ID01"='A' AND "T2_ID02"='B')

CAS 2

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
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
WITH T3 AS (SELECT T2_ID01 FROM T2 WHERE T2_ID01='A' AND T2_ID02 = 'B')
(
  (
  SELECT DISTINCT T1_ID03,T1_COL01
  FROM T1
  WHERE 
  T1_ID01='A' AND T1_ID02 = 'B' AND T1_COL02 = 'C' AND T1_COL03 > 100
  AND T1_ID03 IN (SELECT T2_ID01 FROM T3)
  )
)
UNION
(
  (
  SELECT DISTINCT T1_ID03,T1_COL01
  FROM T1
  WHERE 
  T1_ID01='A' AND T1_ID02 = 'B' AND T1_COL02 = 'D' AND T1_COL03 > 252
  AND T1_ID03 IN (SELECT T2_ID01 FROM T3)
  )
)
UNION
(
  (
  SELECT DISTINCT T1_ID03,T1_COL01
  FROM T1
  WHERE 
  T1_ID01='A' AND T1_ID02 = 'B' AND T1_COL02 = 'F' AND T1_COL03 > 586
  AND T1_ID03 IN (SELECT T2_ID01 FROM T3)
  )
)
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |     3 |    93 |    20  (75)| 00:15:37 |
|   1 |  TEMP TABLE TRANSFORMATION      |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                | SYS_TEMP_0FD9D6679_3A8CCF3C |       |       |            |          |
|*  3 |    INDEX RANGE SCAN             | SYS_C00592543               |  1000 |  6000 |     2   (0)| 00:01:37 |
|   4 |   SORT UNIQUE                   |                             |     3 |    93 |    20  (75)| 00:15:37 |
|   5 |    UNION-ALL                    |                             |       |       |            |          |
|*  6 |     HASH JOIN                   |                             |     1 |    31 |     6  (17)| 00:04:25 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| T1                          |     1 |    28 |     3   (0)| 00:02:25 |
|*  8 |       INDEX RANGE SCAN          | SYS_C00592542               |   160 |       |     2   (0)| 00:01:37 |
|   9 |      VIEW                       |                             |  1000 |  3000 |     2   (0)| 00:01:37 |
|  10 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6679_3A8CCF3C |  1000 |  3000 |     2   (0)| 00:01:37 |
|* 11 |     HASH JOIN                   |                             |     1 |    31 |     6  (17)| 00:04:25 |
|* 12 |      TABLE ACCESS BY INDEX ROWID| T1                          |     1 |    28 |     3   (0)| 00:02:25 |
|* 13 |       INDEX RANGE SCAN          | SYS_C00592542               |   160 |       |     2   (0)| 00:01:37 |
|  14 |      VIEW                       |                             |  1000 |  3000 |     2   (0)| 00:01:37 |
|  15 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6679_3A8CCF3C |  1000 |  3000 |     2   (0)| 00:01:37 |
|* 16 |     HASH JOIN                   |                             |     1 |    31 |     6  (17)| 00:04:25 |
|* 17 |      TABLE ACCESS BY INDEX ROWID| T1                          |     1 |    28 |     3   (0)| 00:02:25 |
|* 18 |       INDEX RANGE SCAN          | SYS_C00592542               |   160 |       |     2   (0)| 00:01:37 |
|  19 |      VIEW                       |                             |  1000 |  3000 |     2   (0)| 00:01:37 |
|  20 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6679_3A8CCF3C |  1000 |  3000 |     2   (0)| 00:01:37 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2_ID01"='A' AND "T2_ID02"='B')
   6 - access("T1_ID03"="T2_ID01")
   7 - filter("T1_COL02"='C' AND "T1_COL03">100)
   8 - access("T1_ID01"='A' AND "T1_ID02"='B')
  11 - access("T1_ID03"="T2_ID01")
  12 - filter("T1_COL02"='D' AND "T1_COL03">252)
  13 - access("T1_ID01"='A' AND "T1_ID02"='B')
  16 - access("T1_ID03"="T2_ID01")
  17 - filter("T1_COL02"='F' AND "T1_COL03">586)
  18 - access("T1_ID01"='A' AND "T1_ID02"='B')

13/05/2012, 11h20
ojo77

1°) Il ne faut pas faire aveuglément confiance aux plans d'exécution : ce n'est pas parce que le coût est plus élevé que c'est pire.

2°) avant d'aller plus loin, il faudrait tester :

flush shared pool, flush buffer cache, timing on autotrace trace only et requête

A partir de là vous devriez savoir laquelle est effectivement optimale.

Ensuite la factorisation par l'utilisation d'une vue en ligne n'a d'intérêt que pour la lisibilité du code (cet avis n'engage que moi). A votre place, je chagerais le "in" en jointure et regrouperais les union en une seule requête, mais comme le but n'est pas de réécrire la requête je vous renvoie à l'expérimentation.
14/05/2012, 08h59
mnitu

Jonathan Lewis a publié plusieurs commentaires sur le comportement de subquery factoring sur son blog.

Citation:

ma sous-requête de la clause WITH est exécutée qu’une seule fois alors que celle du CAS 1 , 3 fois.

Et comment avez vous conclu qu'elle n'est exécutée qu'une seule fois ? N'est-ce pas 4 fois d'après ce que vous avez posté comme plan d'exécution?

Vous avez utilisé la clause with (ou subquery factoring) que vous avez nommée T3 ; à l’exécution de votre select le CBO (Oracle Optimizer), à chaque fois qu’il rencontre le nom ‘T3’, aura le choix entre (a) remplacer ‘T3’ par la définition que vous lui avez attribuée dans la clause with ou (b) créer une table temporaire contenant le résultat du select. Pour passer d’une situation à l’autre vous disposez de deux hints, /*+ inline */ et /*+ materialized */ respectivement.

cas 1 (sans confusion aucune avec votre cas 1)

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
34
35
36
37
38
39
40
41
42
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  1gbf6xjasy41n, child number 0
-------------------------------------
WITH T3 AS (SELECT /*+ inline */ T2_ID01 FROM T2 WHERE T2_ID01='A' AND T2
----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |       |       |    12 (100)|          |
|   1 |  SORT UNIQUE           |               |     3 |   108 |    12  (75)| 00:00:01 |
|   2 |   UNION-ALL            |               |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN|               |     1 |    37 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | T1            |     1 |    28 |     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT        |               |     1 |     9 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN  | SYS_C00195228 |     1 |     9 |     1   (0)| 00:00:01 |
|   7 |    MERGE JOIN CARTESIAN|               |     1 |    37 |     3   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL  | T1            |     1 |    28 |     2   (0)| 00:00:01 |
|   9 |     BUFFER SORT        |               |     1 |     9 |     1   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN  | SYS_C00195228 |     1 |     9 |     1   (0)| 00:00:01 |
|  11 |    MERGE JOIN CARTESIAN|               |     1 |    34 |     3   (0)| 00:00:01 |
|* 12 |     TABLE ACCESS FULL  | T1            |     1 |    28 |     2   (0)| 00:00:01 |
|  13 |     BUFFER SORT        |               |     1 |     6 |     1   (0)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN  | SYS_C00195228 |     1 |     6 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_COL02"='C' AND
              "T1_ID03"='A' AND "T1_COL03">100))
   6 - access("T2_ID01"='A' AND "T2_ID02"='B')
   8 - filter(("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_COL02"='D' AND
              "T1_ID03"='A' AND "T1_COL03">252))
  10 - access("T2_ID01"='A' AND "T2_ID02"='B')
  12 - filter(("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_COL02"='F' AND
              "T1_ID03"='A' AND "T1_COL03">586))
  14 - access("T2_ID01"='A' AND "T2_ID02"='B')
 
Note
-----
   - dynamic sampling used for this statement

Il y a bien 3 accès à la table (en réalité à son INDEX SYS_C00195228)

cas 2 (sans confusion aucune avec votre cas 2)

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
34
35
36
37
38
39
40
41
42
43
44
45
46
 
----------------------------------------------------------------------------------------------------------
SQL_ID  gu8qxapr1wf72, child number 0
-------------------------------------
WITH T3 AS (SELECT /*+ materialize */ T2_ID01 FROM T2 WHERE T2_ID01='A' Plan hash value: 991480293
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |       |       |    12 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |          |
|   2 |   LOAD AS SELECT               |                             |       |       |            |          |
|*  3 |    TABLE ACCESS FULL           | T2                          |     1 |     6 |     2   (0)| 00:00:01 |
|   4 |   SORT UNIQUE                  |                             |     3 |    93 |    12  (75)| 00:00:01 |
|   5 |    UNION-ALL                   |                             |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| T1                          |     1 |    28 |     1   (0)| 00:00:01 |
|   7 |      NESTED LOOPS              |                             |     1 |    31 |     3   (0)| 00:00:01 |
|   8 |       VIEW                     |                             |     1 |     3 |     2   (0)| 00:00:01 |
|   9 |        TABLE ACCESS FULL       | SYS_TEMP_0FD9D7730_BA836935 |     1 |     3 |     2   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN         | SYS_C00195227               |     1 |       |     1   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID| T1                          |     1 |    28 |     1   (0)| 00:00:01 |
|  12 |      NESTED LOOPS              |                             |     1 |    31 |     3   (0)| 00:00:01 |
|  13 |       VIEW                     |                             |     1 |     3 |     2   (0)| 00:00:01 |
|  14 |        TABLE ACCESS FULL       | SYS_TEMP_0FD9D7730_BA836935 |     1 |     3 |     2   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN         | SYS_C00195227               |     1 |       |     1   (0)| 00:00:01 |
|* 16 |     TABLE ACCESS BY INDEX ROWID| T1                          |     1 |    28 |     1   (0)| 00:00:01 |
|  17 |      NESTED LOOPS              |                             |     1 |    31 |     3   (0)| 00:00:01 |
|  18 |       VIEW                     |                             |     1 |     3 |     2   (0)| 00:00:01 |
|  19 |        TABLE ACCESS FULL       | SYS_TEMP_0FD9D7730_BA836935 |     1 |     3 |     2   (0)| 00:00:01 |
|* 20 |       INDEX RANGE SCAN         | SYS_C00195227               |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("T2_ID01"='A' AND "T2_ID02"='B'))
   6 - filter(("T1_COL02"='C' AND "T1_COL03">100))
  10 - access("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_ID03"="T2_ID01")
  11 - filter(("T1_COL02"='D' AND "T1_COL03">252))
  15 - access("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_ID03"="T2_ID01")
  16 - filter(("T1_COL02"='F' AND "T1_COL03">586))
  20 - access("T1_ID01"='A' AND "T1_ID02"='B' AND "T1_ID03"="T2_ID01")
 
Note
-----
   - dynamic sampling used for this statement

où il y a création d'une table temporaire SYS_TEMP_0FD9D7730_BA836935 et 3 accès à cette table temporaire.

Le lien de Jonathan Lewis cité par mnitu est un lien très intéressant pour comprendre les différentes subtilités des clauses with.