Performances et DELETE massifs

Il arrive souvent d’être amené à supprimer un grand nombre de lignes dans une ou plusieurs tables d’une base de données. Ces suppressions massives deviennent souvent de gros soucis en termes de performance.

Il faut bien avoir à l’esprit que le DELETE est surement l’instruction la plus couteuse dans une base Oracle, et ce pour plusieurs raisons :
- Le DELETE implique une recherche des lignes à updater (équivalent à une instruction SELECT)
- Les entrées d’index qui référencent les lignes updater doivent être supprimées de l’index
- Le code des triggers BEFORE ou AFTER DELETE est exécuté
- Si des lignes d’une autre table référencent les lignes à updater et que la FK est définie en mode « ON DELETE CASCADE » alors ces lignes doivent aussi être supprimées de la table et de ses index. Les triggers basés sur les instructions DELETE pour ces tables « filles » sont aussi déclenchés.
- De l’UNDO et du REDO sont générés

Donc à cause de tout ce travail qu’Oracle doit effectuer lors d’un DELETE il est recommandé d’éviter tout DELETE massif. J’entend par DELETE massif la suppression d’une grosse (voire d’une majeure) partie de la table.

A la place d’effectuer un DELETE il est préférable de créer une autre table qui va contenir uniquement les lignes à conserver pour pouvoir ensuite soit truncater soit dropper la table initiale.

TEST CASE :

Voici un petit test case pour illustrer tout ça.
Tout d’abord on construit une table volumineuse qui servira au DELETE :

SQL> create table t1 as select * from all_objects;

Table created.

Elapsed: 00:00:02.45

SQL> insert into t1 select * from t1;

71718 rows created.

Elapsed: 00:00:00.92
SQL> /

143436 rows created.

Elapsed: 00:00:02.40
SQL> /

286872 rows created.

Elapsed: 00:00:06.03
SQL> /

573744 rows created.

Elapsed: 00:00:11.29
SQL> /

1147488 rows created.

Elapsed: 00:00:21.04

SQL> select count(1) from t1;

  COUNT(1)
----------
   2294976

1 row selected.

SQL> select count(1) from t1 where object_type in ('SYNONYM','JAVA CLASS','VIEW','INDEX');

  COUNT(1)
----------
   1908864

SQL> create index idx_obj_typ on t1(OBJECT_TYPE);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

On a là une table T1 d’environ 2 millions de lignes et on souhaiterait supprimer les lignes dont la colonne OBJECT_TYPE est l’un des types suivant : SYNONYM, JAVA CLASS, VIEW et INDEX.
Ces lignes représentent à peu près 80% de la table.

Effectuons d’abord le test du DELETE:

SQL> DELETE T1 where object_type in ('SYNONYM','JAVA CLASS','VIEW','INDEX');
Elapsed: 00:02:16.59

Le DELETE a mis plus de 2 minutes à s’exécuter sur ma petite base 11g R2.

Voyons maintenant ce que donne la méthode du CTAS (CREATE TABLE AS SELECT) après avoir rollbacké la transaction précédente :

SQL> rollback;
Elapsed: 00:02:58.71

SQL> create table t1_new as select * from t1 where OBJECT_TYPE not in ('SYNONYM','JAVA CLASS','VIEW','INDEX');
Elapsed: 00:00:04.65
SQL> truncate table t1;
Elapsed: 00:00:00.34

La sauvegarde des lignes à conserver a mis plus de 4 secondes, et le TRUNCATE de la table T1 est quasi-instantanée.
Ensuite pour obtenir une table T1 avec uniquement les lignes à conserver vous avez 2 choix :
- Le premier choix consiste à renommer la table T1_NEW en T1 après avoir droppé la table T1 d’origine. Bien sûr cela implique de recréer les objets liés à la table (contraintes, indexes, triggers etc.)
- Le deuxième option consiste à réinsérer les lignes sauvegardées depuis T1_NEW vers T1. Pour accélérer l’insert on peut utiliser l’INSERT en mode DIRECT PATH via le hint APPEND. Cette méthode permet d’écrire directement après le High Water Mark en bypasant le buffer cache.

Voici ce que donne la 2ème méthode :

SQL> insert /*+ append */ into t1 select * from t1_new;
Elapsed: 00:00:05.31
SQL> commit;
Elapsed: 00:00:00.26

L’insert a pris 5 secondes.
Si on comptabilise le temps lié au CTAS+TRUNCATE+INSERT on arrive à environ 10 secondes ce qui est bien mieux que les 2 minutes du DELETE d’autant plus que plus la table est volumineuse et plus le DELETE sera long comparé au CTAS.

L’utilisation de cette méthode permet aussi d’éviter la fragmentation liée aux opérations DELETE. En effet, les DELETE ne réinitialisent pas le High Water Mark (HWM) à l’inverse du TRUNCATE. Un HWM non resetté rend les futurs accès en Full Table Scan beaucoup plus couteux puisqu’on va récupérer en plus des blocks contenant des lignes tous les blocks vides qui contenaient les lignes supprimées

Il existe une manière encore plus rapide que celle que j’ai décrite précédemment mais cela concerne uniquement les tables partitionnées.
En effet, il est possible de se débarrasser aisément des lignes qui ne sont plus désirées dans la table en supprimant directement la ou les partitions qui les référencent.
Imaginons une table historisant les ventes de voitures d’un concessionnaire. La table est partitionnée « par mois » selon la date de vente du véhicule. La société ne désire conserver que 2 mois d’historique. Au lieu d’effectuer des DELETE il est possible de dropper directement les partitions correspondant à des mois antérieures aux 2 derniers mois. Cette opération est instantanée.

Pour illustrer le DROP de partitions reconstruisons notre table T1 en effectuant un partitionnement « BY LIST » sur la colonne OBJECT_TYPE :

SQL> drop table t1 purge;
Elapsed: 00:00:00.07
 
SQL> create table t1
  2  (
  3    OWNER          VARCHAR2(30) not null,
  4    OBJECT_NAME    VARCHAR2(30) not null,
  5    SUBOBJECT_NAME VARCHAR2(30),
  6    OBJECT_ID      NUMBER not null,
  7    DATA_OBJECT_ID NUMBER,
  8    OBJECT_TYPE    VARCHAR2(19),
  9    CREATED        DATE not null,
 10    LAST_DDL_TIME  DATE not null,
 11    TIMESTAMP      VARCHAR2(19),
 12    STATUS         VARCHAR2(7),
 13    TEMPORARY      VARCHAR2(1),
 14    GENERATED      VARCHAR2(1),
 15    SECONDARY      VARCHAR2(1),
 16    NAMESPACE      NUMBER not null,
 17    EDITION_NAME   VARCHAR2(30)
 18  )
 19   partition by list(object_type)
 20   ( partition p_SYNONYM values ( 'SYNONYM' ),
 21   partition p_JAVA_CLASS values ( 'JAVA CLASS' ),
 22   partition p_VIEW values ( 'VIEW' ),
 23   partition p_INDEX values ( 'INDEX' ),
 24   partition p_TABLE values ( 'TABLE' ),
 25   partition p_TYPE values ( 'TYPE' ),
 26   partition p_PACKAGE values ( 'PACKAGE' ),
 27   partition p_PACKAGE_BODY values ( 'PACKAGE BODY' ),
 28   partition p_JAVA_RESOURCE values ( 'JAVA RESOURCE' ),
 29   partition p_TRIGGER values ( 'TRIGGER' ),
 30   partition p_INDEX_PARTITION values ( 'INDEX PARTITION' ),
 31   partition p_FUNCTION values ( 'FUNCTION' ),
 32   partition p_JAVA_DATA values ( 'JAVA DATA' ),
 33   partition p_TABLE_PARTITION values ( 'TABLE PARTITION' ),
 34   partition p_TYPE_BODY values ( 'TYPE BODY' ),
 35   partition p_SEQUENCE values ( 'SEQUENCE' ),
 36   partition p_LIBRARY values ( 'LIBRARY' ),
 37   partition p_PROCEDURE values ( 'PROCEDURE' ),
 38   partition p_XML_SCHEMA values ( 'XML SCHEMA' ),
 39   partition p_OPERATOR values ( 'OPERATOR' ),
 40   partition p_RULE_SET values ( 'RULE SET' ),
 41   partition p_PROGRAM values ( 'PROGRAM' ),
 42   partition p_EVALUATION_CONTEXT values ( 'EVALUATION CONTEXT' ),
 43   partition p_JOB values ( 'JOB' ),
 44   partition p_JOB_CLASS values ( 'JOB CLASS' ),
 45   partition p_CLUSTER values ( 'CLUSTER' ),
 46   partition p_WINDOW values ( 'WINDOW' ),
 47   partition p_CONTEXT values ( 'CONTEXT' ),
 48   partition p_DIRECTORY values ( 'DIRECTORY' ),
 49   partition p_INDEXTYPE values ( 'INDEXTYPE' ),
 50   partition p_SCHEDULER_GROUP values ( 'SCHEDULER GROUP' ),
 51   partition p_MATERIALIZED_VIEW values ( 'MATERIALIZED VIEW' ),
 52   partition p_SCHEDULE values ( 'SCHEDULE' ),
 53   partition p_DESTINATION values ( 'DESTINATION' ),
 54   partition p_JAVA_SOURCE values ( 'JAVA SOURCE' ),
 55   partition p_CONSUMER_GROUP values ( 'CONSUMER GROUP' ),
 56   partition p_RULE values ( 'RULE' ),
 57   partition p_EDITION values ( 'EDITION' ),
 58    partition p_DEFAULT values ( DEFAULT )
 59   )
 60   ;
Elapsed: 00:00:00.06

Insert into t1 select * from t1_save;

SQL> Insert into t1 select * from t1_save;
Elapsed: 00:01:54.01

Pour supprimer les mêmes lignes que dans notre premier test, il suffit de supprimer les partitions correspondants aux OBJECT_TYPE concernés par la suppression. Pour ce faire on utilise la clause DROP PARTITION de la commande ALTER TABLE :

SQL> alter table T1 drop partition (p_SYNONYM);
Elapsed: 00:00:00.26
SQL> alter table T1 drop partition (p_JAVA_CLASS);
Elapsed: 00:00:00.00
SQL> alter table T1 drop partition (p_VIEW);
Elapsed: 00:00:00.00
SQL> alter table T1 drop partition (p_INDEX);
Elapsed: 00:00:00.01

Peu importe la taille des partitions le DROP de partitions sera toujours quasiment instantanée.
Comparé au DELETE il n’y a pas photo.


CONCLUSION :
Pour supprimer un grand nombre de lignes dans une table, le DELETE constitue presque toujours la plus mauvaise solution. La bonne approche consiste soit à privilégier la suppression de partition dans le cas où la table est partitionnée, soit à reconstruire la table avec uniquement les lignes à conserver. En plus d’un gain en temps d’exécution cette approche permet d’éviter la fragmentation des tables.