Introduction
Dans mon précédent article "ORDER BY et Consistents gets réduits par 10 : un ORDER BY ne ralentit pas toujours une requête!", je parlais du fait qu'un ORDER BY ne ralentit pas toujours un SELECT, voir même peut l'accélérer en divisant considérablement le nombre de Consistents gets. Dans cet article nous allons voir que le ORDER BY peut aussi diminuer la taille des données échangées entre le client et le serveur via de la déduplication des données identiques et que cela contribue à rendre le SELECT plus rapide.



Points d'attention
Réfléchissez bien avant de faire un ORDER BY car cette opération est très lourde pour Oracle et dans certains cas cela alourdit considérablement le temps d'exécution.



Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
Création de notre base de test
============================================================================================
La base de test est un extract de quelques colonnes de DBA_OBJECTS. Je recopie à l'identique ces données jusqu'à avoir 630 000 enregistrements dans ma table.
          SQL> create table test_obj02 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;

          SQL> insert into test_obj02 select * from test_obj02;
          78417 rows created.

          SQL> /
          ...
          ...

          SQL> select count(*) from test_obj02;
          COUNT(*)
          ----------
          628280


============================================================================================
SELECT sans le ORDER BY
============================================================================================

On génère les stats du schéma de la table.
          SQL> exec dbms_stats.gather_schema_stats('HR');

          SQL> set autotrace traceonly explain statistics
          SQL> set timing on

Première exécution du SELECT sans ORDER BY : lecture des données sur disque dur, temps total 04 secondes 52 centièmes pour un coût de 1066 et 32,2 mégas octets échangés sur le réseau.
          SQL> select OWNER, OBJECT_NAME from test_obj02;
          628280 rows selected.

          Elapsed: 00:00:04.52

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3527827752

          --------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT    |                        |   628K|    23M|  1066     (1)| 00:00:01   |
          |   1 |  TABLE ACCESS FULL  | TEST_OBJ02    |   628K|    23M|  1066     (1)| 00:00:01  |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          29  recursive calls
          0  db block gets
          45568  consistent gets
          3894  physical reads
          0  redo size
          32283093  bytes sent via SQL*Net to client
          461343  bytes received via SQL*Net from client
          41887  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          628280  rows processed

Deuxième exécution du SELECT sans ORDER BY : les données sont lues uniquement en mémoire, temps total 04 secondes 34 centièmes pour un coût de 1066. Les physical reads sont à zéro et 32,2 mégas octets échangés sur le réseau..
          SQL> /
          628280 rows selected.

          Elapsed: 00:00:04.34

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3527827752

          ------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
          ------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT   |                       |   628K|    23M|  1066     (1)| 00:00:01   |
          |   1 |  TABLE ACCESS FULL | TEST_OBJ02  |   628K|    23M|  1066     (1)| 00:00:01    |
          ------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45517  consistent gets
          0  physical reads
          0  redo size
          32283093  bytes sent via SQL*Net to client
          461343  bytes received via SQL*Net from client
          41887  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          628280  rows processed

Troisième exécution du SELECT sans ORDER BY : les données sont lues uniquement en mémoire, temps total 04 secondes 07 centièmes pour un coût de 1066. Les physical reads sont à zéro et 32,2 mégas octets échangés sur le réseau..
          SQL> /
          628280 rows selected.

          Elapsed: 00:00:04.07

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3527827752

          --------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT    |                       |   628K |    23M|  1066     (1)| 00:00:01   |
          |   1 |  TABLE ACCESS FULL  | TEST_OBJ02   |   628K|    23M|  1066     (1)| 00:00:01   |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45517  consistent gets
          0  physical reads
          0  redo size
          32283093  bytes sent via SQL*Net to client
          461343  bytes received via SQL*Net from client
          41887  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          628280  rows processed


============================================================================================
SELECT avec le ORDER BY
============================================================================================

On va maintenant exécuter le même ordre SQL mais en ajoutant un ORDER BY. Oracle ajoute une opération de SORT avec un coût de 7491, soit plus de sept fois le coût de lecture de toutes les données de la table. Cependant la quantité de données échangées serveur/client est divisée par deux avec seulement 14 mégas octets. On remarque que les physicals reads sont à 0 pour la première exécution car les données sont déjà en mémoire suite aux précédents SELECTs.

          SQL> select OWNER, OBJECT_NAME from test_obj02 order by owner, object_name;
          628280 rows selected.

          Elapsed: 00:00:03.95

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3934302124

          -----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name             | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    |
          -----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                       |   628K|    23M|                 |  7491   (1)| 00:00:01  |
          |   1 |  SORT ORDER BY         |                       |   628K|    23M|    28M       |  7491   (1)| 00:00:01  |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ02  |   628K|    23M|                  |  1066   (1)| 00:00:01  |
          -----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3893  consistent gets
          0  physical reads
          0  redo size
          13772046  bytes sent via SQL*Net to client
          461343  bytes received via SQL*Net from client
          41887  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          628280  rows processed

Deuxième exécution.
          SQL> /
          628280 rows selected.

          Elapsed: 00:00:03.92

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3934302124

          -------------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name              | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    |
          -------------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                        |   628K|    23M|                 |  7491   (1)| 00:00:01   |
          |   1 |  SORT ORDER BY         |                        |   628K|    23M|    28M       |  7491   (1)| 00:00:01   |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ02   |   628K|    23M|                 |  1066   (1)| 00:00:01   |
          -------------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3893  consistent gets
          0  physical reads
          0  redo size
          13772046  bytes sent via SQL*Net to client
          461343  bytes received via SQL*Net from client
          41887  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          628280  rows processed

Troisième exécution.
          SQL> /
          628280 rows selected.

          Elapsed: 00:00:03.95

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3934302124

          -------------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                         | Name              | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    |
          -------------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT      |                        |   628K|    23M|                |  7491   (1)| 00:00:01   |
          |   1 |  SORT ORDER BY          |                        |   628K|    23M|    28M      |  7491   (1)| 00:00:01   |
          |   2 |   TABLE ACCESS FULL   | TEST_OBJ02   |   628K|    23M|                |  1066   (1)| 00:00:01   |
          -------------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3893  consistent gets
          0  physical reads
          0  redo size
          13772046  bytes sent via SQL*Net to client
          461343  bytes received via SQL*Net from client
          41887  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          628280  rows processed


============================================================================================
Comparaison entre les deux SELECT : le ORDER BY ne ralentit pas le SELECT
============================================================================================

Nous allons maintenant comparer les temps d'exécution après le premier SELECT puisque lors du premier les données sont sur disque dur et pas lors des SELECT suivants.

Sans ORDER BY

          Temps d'exécution : 04,34 secondes et 04,07 secondes
          Consistent gets : 45517 et 45517         
          Octets échangés : 32,28 mégas octets et 32,28 mégas octets

Avec ORDER BY
          Temps d'exécution : 03,92 secondes et 03,95 secondes
          Consistent gets : 3893 et 3893

          Octets échangés : 13,77 mégas octets et 13,77 mégas octets
          Opération en plus : SORT ORDER BY pour un coût de
7491


Nous constatons que le SELECT avec le ORDER BY est plus rapide de presque 10% que le SELECT sans ORDER BY alors qu'il y a une opération de SORT qui représente en coût plus de 7 fois le coût d'un FULL TABLE SCAN de la table de tests!

Comment l'expliquer? De deux façons :
          - le nombre de Consistents gets est dix fois moins importants, chose détaillée dans cet article "ORDER BY et Consistents gets réduits par 10 : un ORDER BY ne ralentit pas toujours une requête!"
          - la quantité de données échangée entre le serveur et le client est divisé par plus de deux : 13,77 Mo contre 32,28 Mo alors que dans mon article ci-dessus ce phénomène n'existait pas. L'explication est simple : le protocole réseau SQLNet fait par défaut de la déduplication de données, c'est à dire que si deux enregistrements qui se suivent sont identiques alors Oracle en supprime un. Et c'est justement ce que le ORDER BY provoque dans un SELECT, les enregistrements identiques se suivent et donc Oracle peut supprimer les doublons, triplés... A quelle hauteur? 32,28 - 13,77 = 18,51 mégas octets soit plus de la moitié des données sélectionnées!

Attention, cette fonctionnalité étant gratuite, ne vous attendez pas à des performances extraordinaires! Il faut que les enregistrements soient absolument identiques sinon il n'y a aucun bénéfice, c'est pourquoi on parle de déduplication et pas de compression. Dans mon article précédent (voir lien ci-dessus), le contenu de ma table TEST_OBJ01 est différent de TEST_OBJ02 car j'avais ajouté un ID basé sur ROWNUM dans la colonne OWNER, ce qui fait que chaque enregistrement différait par cette colonne, comme on le voit ici
          SQL> select OWNER, OBJECT_NAME from test_obj01 where rownum < 11;
          OWNER             OBJECT_NAME
          -------------------- --------------------
          SYS1             I_FILE#_BLOCK#
          SYS2             I_OBJ3
          SYS3             I_TS1
          SYS4             I_CON1
          SYS5             IND$


Voyons maintenant comment Oracle retourne les dernières données selon les deux SELECTs.
Sans ORDER BY, aucun enregistrement consécutifs identiques.
          SQL> select OWNER, OBJECT_NAME from TEST_OBJ02;
          OWNER             OBJECT_NAME
          -------------------- --------------------
          ...
         
SYS             jdk/internal/dynalink/linker/TypeBasedGuardingDynamicLinker
          SYS             jdk/internal/dynalink/beans/DynamicMethod
          SYS             jdk/internal/dynalink/beans/BeanIntrospector
          SYS             jdk/internal/dynalink/beans/BeanLinker$Binder
          SYS             jdk/internal/dynalink/beans/BeanLinker$1
          SYS             jdk/internal/dynalink/beans/BeanLinker$CollectionType
          SYS             jdk/internal/dynalink/beans/BeanLinker
          SYS             jdk/internal/dynalink/beans/ClassLinker
          SYS             jdk/internal/dynalink/beans/DynamicMethodLinker
          SYS             jdk/internal/dynalink/beans/BeansLinker$1


Avec ORDER BY, de nombreux enregistrements consécutifs identiques sur lesquels la déduplication d'SQLNet va pouvoir agir.
          SQL> select OWNER, OBJECT_NAME from TEST_OBJ02 order by OWNER, OBJECT_NAME;
          OWNER           OBJECT_NAME
          -------------------- --------------------
          ...
          XFILES             XMLSCHEMA_LIST
          XFILES             XMLSCHEMA_LIST
          XFILES             XMLSCHEMA_LIST
          XFILES             XMLSCHEMA_LIST
          XFILES             XMLSCHEMA_LIST
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_LIST_DML
          XFILES             XMLSCHEMA_OBJECT_LIST
          XFILES             XMLSCHEMA_OBJECT_LIST
          XFILES             XMLSCHEMA_OBJECT_LIST
          XFILES             XMLSCHEMA_OBJECT_LIST

Continuons notre analyse. La table a 628280 enregistrements mais, si on groupe par OWNER et OBJECT_NAME, on tombe à 76200 soit une moyenne de 8,24 occurrences pour chaque enregistrement. Avec un tel taux de lignes dupliqués, on s'attendrait à avoir une taille divisée par 8 soit 4 mégas au lieu de 32 mais ce n'est pas le cas; pas d'explication pour le moment, peut-être dans un prochain article :-)
          SQL> select count(*), OWNER, OBJECT_NAME from TEST_OBJ02 group by OWNER, OBJECT_NAME order by count(*), owner, object_name;
          COUNT(*) OWNER                OBJECT_NAME                                                         
          ---------- -------------------- --------------------                                                
          8      APEX_050100          APEX                                                                
          8      APEX_050100          APEX$ARCHIVE_CONTENTS                                                                   
          8      APEX_050100          APEX$ARCHIVE_CONTENTS_IDX1                                                              
          8      APEX_050100          APEX$ARCHIVE_HEADER
         ...
          72    SYS                        WRI$_OPTSTAT_HISTGRM_HISTORY                                                            
          80    SYS                        SYS_IL0000014347C00016$$                                                                
          80    SYS                        SYS_LOB0000014347C00016$$                                                               
          80    SYS                        WRI$_OPTSTAT_HISTHEAD_HISTORY                                                           
         272   SYS                        WRI$_OPTSTAT_SYNOPSIS$                                                                  
         808   SYS                        AQ$_SUBSCRIBER_LWM

         76200 rows selected.