Discussion :

[Casshern]

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 :

1. 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.
2. 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 :

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

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 :

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
(
  (
  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 :

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
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')