With et performances

Bonjour,

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

Citation:

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

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

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'
Citation:

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'
Citation:

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 :

Citation:

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 :

Citation:

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.

Merci pour l'explication. :king:

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

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

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