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 lespace alloué à la table, cet espace est directement rendu au système dexploitation. Les tablespaces de PostgreSQL sont uniquement des moyens de désigner un dossier et non une véritable couche logique.
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 lespace est proche de celui observé avec PostgreSQL. Lespace alloué nest pas rendu au système dexploitation mais il est rendu immédiatement au tablespace après le TRUNCATE. Il ne reste alors quun 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 dun TRUNCATE avec la syntaxe propriétaire DROP ALL STORAGE.