With et performances

**Sckizo** · 05/05/2008, 11h27

Bonjour,

Je lis dans le sujet Mettre les résultats en colonne qu'Orafrance dit

Le WITH permettant souvent d'améliorer les perfs

J'aurais voulu en savoir plus.
Comment cela se fait ce ? et dans quel mesure cela améliore t'il les perfs ?

C'est pour savoir si je me casse le Q à bataillé avec le client pour lui faire admettre une "nouveauté" de plus ou pas

**philcero** · 05/05/2008, 11h43

Le WITH sert à "matérialiser" une sous-requête le temps de ta requête maître. Autrement dit si tu as plusieurs références à une même sous-requête Oracle ne l'effectuera qu'une seule fois et mutualisera le résultat à toute ta requête.

C'est très utile lors des requêtes récursives. Cherche dans la documentation SUBQUERY FACTORING pour plus d'informations (documentation SQL REFERENCE/SELECT).

**orafrance** · 05/05/2008, 15h01

Pour faire simple, Oracle lance la requête du WITH et garde le résultat de coté, ainsi qu'on l'utilise 2 fois ou 10 fois les perfs sont sensiblement moins affectées que si la requête devait être exécutée 10 fois

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
 
SELECT object_name, object_type
FROM dba_objects
WHERE object_type = 'TABLE'
  AND owner = 'SYS'
UNION
SELECT object_name, object_type
FROM dba_objects
WHERE object_type = 'INDEX'
  AND owner = 'SYS'
UNION
SELECT object_name, object_type
FROM dba_objects
WHERE object_type = 'VIEW'
  AND owner = 'SYS'
UNION
SELECT object_name, object_type
FROM dba_objects
WHERE object_type = 'PROCEDURE'
  AND owner = 'SYS'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.15 0.18 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 183 0.42 0.68 0 7924 0 2722
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 185 0.57 0.87 0 7924 0 2722

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
WITH all_obj AS (
SELECT object_name, object_type
FROM dba_objects
WHERE owner = 'SYS'
)
SELECT * FROM all_obj 
WHERE object_type = 'TABLE'
UNION
SELECT * FROM all_obj 
WHERE object_type = 'INDEX'
UNION
SELECT * FROM all_obj 
WHERE object_type = 'VIEW'
UNION
SELECT * FROM all_obj 
WHERE object_type = 'PROCEDURE'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.08 0 0 1 0
Execute 1 0.09 0.11 0 0 3 0
Fetch 183 0.37 0.68 16 74 1 2722
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 185 0.56 0.89 16 74 5 2722

Regarde la colonne disk dans le 2eme cas

Pour info voici les plans :

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 UNION-ALL
3 2 VIEW OF 'DBA_OBJECTS'
4 3 UNION-ALL
5 4 FILTER
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
8 7 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
9 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
10 9 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
12 11 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
13 4 FILTER
14 13 NESTED LOOPS
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
16 15 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
17 14 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
18 2 VIEW OF 'DBA_OBJECTS'
19 18 UNION-ALL
20 19 FILTER
21 20 NESTED LOOPS
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
23 22 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
24 21 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
25 24 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
26 20 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
27 26 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
28 19 FILTER
29 28 NESTED LOOPS
30 29 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
31 30 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
32 29 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
33 2 VIEW OF 'DBA_OBJECTS'
34 33 UNION-ALL
35 34 FILTER
36 35 NESTED LOOPS
37 36 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
38 37 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
39 36 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
40 39 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
41 35 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
42 41 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
43 34 FILTER
44 43 NESTED LOOPS
45 44 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
46 45 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
47 44 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)
48 2 VIEW OF 'DBA_OBJECTS'
49 48 UNION-ALL
50 49 FILTER
51 50 NESTED LOOPS
52 51 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
53 52 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
54 51 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
55 54 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
56 50 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
57 56 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
58 49 FILTER
59 58 NESTED LOOPS
60 59 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
61 60 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE)
62 59 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Avec WITH :

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2 SORT (UNIQUE)
4 3 UNION-ALL
5 4 VIEW
6 5 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6630_966BCD'
7 4 VIEW
8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6630_966BCD'
9 4 VIEW
10 9 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6630_966BCD'
11 4 VIEW
12 11 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6630_966BCD'

Evidemment, ce n'est pas toujours payant puisque la création d'une table temporaire est nécessaire avec un FTS dessus.

**McM** · 05/05/2008, 16h40

Merci pour l'explication.

**orafrance** · 05/05/2008, 16h43

Evidemment, tout le monde aura compris que c'est d'autant plus intéressant que le nombre de lignes retournées par la requête du WITH est restreint et/ou que son résultat est utilisé très souvent

Edit : Notez que le WITH est un opérateur de la norme SQL