With et performances [Résolu]

[Sckizo]

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]

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]

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 :

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 :

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]

Merci pour l'explication.

[orafrance]

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

[philcero]

Petit commentaire supplémentaire, le WITH fait partie de la norme SQL99 et est présent sous Oracle qu'à partir de la 9.2.

[Sckizo]

Messieurs, je vous remercie de ces explication didactique et très clair