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.
Je suis fondamentalement en désaccord avec la conclusion. TRUNCATE est un workaround et en aucun une solution généralisée pour effacer des lignes. Rappelons que TRUNCATE ne garantit pas l'intégrité des données.
RépondreSupprimerImagine tu as une vue materialisee M sur une table T. Si tu rafraichis completement et manuellement la mvue M et qu'en meme tant tu fais un delete, ca va certes te prendre un certain temps, mais tu auras les données consistentes. Si tu fais un truncate pendant un refresh, alors tu n'as aucune idée de ce que tu peux recevoir (une erreur, la moitié des données, aucune donnée, toutes les données).
Idem pour les triggers. Si tu as un "DELETE" trigger, il y a sûrement une raison à celà ;-)
Un dernier "GROS" problème, c'est que tu ne peux donner le droit TRUNCATE à un utilisateur non-propriétaire de la table, tu dois lui donner DROP ANY TABLE
L'article est cependant intéressant pour les bases qui sont "offline" les nuits et week-end et ou tu peux faire un peu ce que tu veux en "mono-utilisateur" à ces moments là.
Quant à drop partition, même si le problème de l'intégrité peut se poser, je suis quand même pour :-)
Deux remarques en passant:
RépondreSupprimer(1) un direct path load (hint append) est silencieusement ignoré lorsque la table destinatrice de l'insert contient un trigger ou une Foreign key constraint
(2) alter table drop partition p1;
a comme conséquence de rendre disable tous les indexes globaux qu'il faudra rebuilder. Je me suis retrouvé l'autre jour dans une application ou le rebuild des indexes globaux prenait enormément de temps. Il faudrait mieux envisager
alter table drop parition p1 update global indexes;
Cela prendra un peu plus de temps mais c'est plus efficace en temps et moins onéreux en réorganisation d'objets que le delete.
Quand on a l'intention de faire des deletes massifs, il faut y penser au moment du design afin d'envisager correctement un partitionnement adéquat.
Bien cordialement
Mohamed Houri
Mohamed, Laurent,
RépondreSupprimerMerci pour vos réactions et votre contribution.
Laurent, c'est vrai que j'aurai dû préciser que la méthode que j'ai mis en avant dans cet article partait du principe que la base devait être down ou en tout cas que la table ne puisse être modifiée par une autre session. De plus je parlais surtout des deletes massifs, ceux qu'on ne fait pas forcément tous les jours mais plutôt le soir ou le week end. L'idée de cet article m'est venu suite à un email qu'un lecteur de ce blog m'a envoyé concernant une purge effectuée sur sa base et qui mettait des dizaines d'heures. J'ai surtout voulu mettre en avant pourquoi le DELETE était une opération couteuse et pourquoi il était préférable de l'éviter lors des opérations de suppression massives. Dans une base transactionnelles et en période de workload il est difficile de les contourner.
Mohamed j'ai effectivement découvert y'a pas longtemps en lisant "Pro Oracle SQL" que les hints APPEND étaient ignorées lors de la présence d'une FK. Il faut le savoir car comme tu le dis c'est ignoré SILENCIEUSEMENT.