TRUNCATE, stockage et identités

Tronquer, quel effet ?

      TRUNCATE permet de supprimer l’ensemble des lignes d’une table. Elle est traditionnellement réputée pour libérer rapidement tout l’espace occupé par la table. Est-ce vrai sous Oracle comme sous PostgreSQL et quel est l’impact de la commande lorsque la table dispose d’une colonne ayant la propriété IDENTITY ? La notion d’IDENTITY est apparue en SQL2003, elle est implémentée par Oracle à partir de la version 12.1. Elle s’apparente aux colonnes autoincrémentées de type SERIAL de PostgreSQL.
      Tout d’abord un test sous PostgreSQL 9.6 :

SELECT version(); version ------------------------------------------------------------------------------------------ PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 ligne) CREATE TABLE t1(c1 SERIAL PRIMARY KEY, c2 INTEGER); CREATE TABLE SELECT last_value FROM t1_c1_seq; last_value ------------ 1 (1 ligne) INSERT INTO t1(c2) VALUES(generate_series(1,99999)); INSERT 0 99999 SELECT last_value FROM t1_c1_seq; last_value ------------ 100000 (1 ligne) \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 3568 kB | (1 ligne) DELETE FROM t1; DELETE 99999 \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 3568 kB | (1 ligne) INSERT INTO t1(c2) VALUES(generate_series(1,99999)); INSERT 0 99999 SELECT last_value FROM t1_c1_seq; last_value ------------ 200000 (1 ligne) \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 3568 kB | (1 ligne) TRUNCATE TABLE t1; TRUNCATE TABLE \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 0 bytes | (1 ligne) SELECT last_value FROM t1_c1_seq; last_value ------------ 200000 (1 ligne) TRUNCATE TABLE t1 RESTART IDENTITY; TRUNCATE TABLE SELECT last_value FROM t1_c1_seq; last_value ------------ 1 (1 ligne) \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-----+-------+--------------+---------+------------- public | t1 | table | postgres | 0 bytes | (1 ligne)

      Avec PostgreSQL, TRUNCATE libère complètement l’espace alloué à la table, cet espace est directement rendu au système d’exploitation. Les tablespaces de PostgreSQL sont uniquement des moyens de désigner un dossier et non une véritable couche logique.
      Par ailleurs, PostgreSQL donne le choix lors du TRUNCATE entre réinitialiser les séquences liées aux colonnes SERIAL (RESTART IDENTITY) ou ne pas le faire (CONTINUE IDENTITY, valeur par défaut).
      A présent un test avec Oracle Database dans sa version 12.1.0.2 :

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options CREATE TABLE t1(c1 INTEGER GENERATED AS IDENTITY PRIMARY KEY, c2 INTEGER); Table created. SELECT bytes FROM user_segments WHERE segment_name = 'T1'; no rows selected select SEQUENCE_NAME from user_sequences; SEQUENCE_NAME -------------------------------------------------------------------------------- ISEQ$$_92235 SELECT last_number FROM user_sequences WHERE sequence_name = 'ISEQ$$_92241'; LAST_NUMBER ----------- 1 INSERT INTO t1(c2) SELECT ROWNUM FROM all_objects, all_objects WHERE ROWNUM < 100000; 99999 rows created. COMMIT; Commit complete. SELECT bytes FROM user_segments WHERE segment_name = 'T1'; BYTES ---------- 2097152 SELECT last_number FROM user_sequences WHERE sequence_name = 'ISEQ$$_92241'; LAST_NUMBER ----------- 100001 DELETE FROM t1; 99999 rows deleted. COMMIT; Commit complete. SELECT bytes FROM user_segments WHERE segment_name = 'T1'; BYTES ---------- 2097152 INSERT INTO t1(c2) SELECT ROWNUM FROM all_objects, all_objects WHERE ROWNUM < 100000; 99999 rows created. COMMIT; Commit complete. SELECT last_number FROM user_sequences WHERE sequence_name = 'ISEQ$$_92241'; LAST_NUMBER ----------- 200001 SELECT bytes FROM user_segments WHERE segment_name = 'T1'; BYTES ---------- 2097152 TRUNCATE table T1; Table truncated. SELECT bytes FROM user_segments WHERE segment_name = 'T1'; BYTES ---------- 65536 SELECT last_number FROM user_sequences WHERE sequence_name = 'ISEQ$$_92241'; LAST_NUMBER ----------- 200001 TRUNCATE TABLE t1 DROP ALL STORAGE; Table truncated. SELECT bytes FROM user_segments WHERE segment_name = 'T1'; no rows selected

      Le résultat au niveau de la gestion de l’espace est proche de celui observé avec PostgreSQL. L’espace alloué n’est pas rendu au système d’exploitation mais il est rendu immédiatement au tablespace après le TRUNCATE. Il ne reste alors qu’un segment de taille minimale. Depuis la version 11.2, Oracle a par ailleurs introduit la notion de création de segment différé. Il est ainsi possible de supprimer complètement le segment associé à la table lors d’un TRUNCATE avec la syntaxe propriétaire DROP ALL STORAGE.
      Par ailleurs, Oracle a implémenté en 12.1 la propriété IDENTITY de la norme SQL2003 mais n’a pas pour l’instant implémenté RESTART / CONTINUE IDENTITY au niveau de sa commande TRUNCATE qui est bien plus ancienne. Le comportement du TRUNCATE d’Oracle est équivalent à un TRUNCATE .. CONTINUE IDENTITY.

Mise à jour : 22/10/2016