TRUNCATE TABLE in Oracle Datenbanken: Freigabe von Speicher - Neues in 11.2.0.2

Ralf Durben, 24.09.2010

Ein TRUNCATE TABLE Kommando löscht zwar Zeilen aus einer Tabelle (und  zwar alle!), ist dem DELETE Kommando aber nicht gleich gestellt. So werden zum Beispiel auch keine DELETE-Trigger ausgelöst. Der Grund dafür ist recht einfach: Ein TRUNCATE TABLE Kommando erklärt einfach den  gesamten für diese Tabelle allokierten Speicher zum Freispeicher, nach  dem Motto "Das was dort noch gespeichert ist, das kenne ich nicht". Die einzelnen Datenzeilen werden also gar nicht einzeln betrachtet und deswegen ist das TRUNCATE Kommando auch so schnell.

In der Ursprungsvariante blieb ein Teil des Speichers für die leere  Tabelle allokiert und der Rest wurde freigegeben. Das Verhalten kann  sehr leicht über die Data Dictionary View DB

create table wdg (nr number,text varchar2(100)) / begin  for i in 1..3000 loop   insert into wdg values (i,'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq');   commit;  end loop; end; / select * from dba_extents where segment_name='WDG' / OWNER  SEGMEN PARTITION_NAME SEGMENT TABLESP EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO ------ ------ -------------- ------- ------- ---------- ---------- ---------- ---------- ---------- ------------ TT     WDG                   TABLE   USERS           0          4       488      65536 8            4 TT     WDG                   TABLE   USERS           1          4       496      65536 8            4 TT     WDG                   TABLE   USERS           2          4       504      65536 8            4 TT     WDG                   TABLE   USERS           3          4       512      65536 8            4 TT     WDG                   TABLE   USERS           4          4       520      65536 8            4 TT     WDG                   TABLE   USERS           5          4       528      65536 8            4 6 rows selected. truncate table wdg / select * from dba_extents where segment_name='WDG' / OWNER  SEGMEN PARTITION_NAME SEGMENT TABLESP EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO ------ ------ -------------- ------- ------- ---------- ---------- ---------- ---------- ---------- ------------ TT     WDG                   TABLE   USERS           0          4       488      65536 8            4

A_EXTENTS ermittelt werden:

In dieser ersten Variante des TRUNCATE TABLE Kommandos wurden also alle  Extents (so werden die einzelnen Speicherstücke für ein Segment in einer Oracle Datenbank genannt), bis auf das erste, deallokiert. Das Ergebnis dieses Kommandos war demnach immer eine leere Tabelle mit einem leeren  aber allokierten ersten Extent.

Dieses ist aber nicht günstig, wenn man weiss, dass in naher Zukunft wieder massiv Daten in diese Tabelle eingeladen werden. Daher wurde vor  geraumer Zeit die Variante eingeführt, dass der Speicher allokiert  bleibt, als Freispeicher für diese Tabelle. Für neue Datenlade-Vorgänge ist also keine Speicherallokierung notwendig. Im Beispiel sieht das dann so aus:

create table wdg (nr number,text varchar2(100))
/
begin
 for i in 1..3000 loop
  insert into wdg values (i,'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq');
  commit;
 end loop;
end;
/
select * from dba_extents where segment_name='WDG'
/


OWNER  SEGMEN PARTITION_NAME SEGMENT TABLESP  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------ -------------- ------- ------- ---------- ---------- ---------- ---------- ---------- ------------
TT     WDG                   TABLE   USERS            0          4        488      65536  8            4
TT     WDG                   TABLE   USERS            1          4        496      65536  8            4
TT     WDG                   TABLE   USERS            2          4        504      65536  8            4
TT     WDG                   TABLE   USERS            3          4        512      65536  8            4
TT     WDG                   TABLE   USERS            4          4        520      65536  8            4
TT     WDG                   TABLE   USERS            5          4        528      65536  8            4

6 rows selected.


truncate table wdg reuse storage
/
select * from dba_extents where segment_name='WDG'
/

OWNER  SEGMEN PARTITION_NAME SEGMENT TABLESP  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------ -------------- ------- ------- ---------- ---------- ---------- ---------- ---------- ------------
TT     WDG                   TABLE   USERS            0          4        488      65536  8            4
TT     WDG                   TABLE   USERS            1          4        496      65536  8            4
TT     WDG                   TABLE   USERS            2          4        504      65536  8            4
TT     WDG                   TABLE   USERS            3          4        512      65536  8            4
TT     WDG                   TABLE   USERS            4          4        520      65536  8            4
TT     WDG                   TABLE   USERS            5          4        528      65536  8            4

6 rows selected.
 

Alle Extents sind also weiterhin der Tabelle WDG zugeordnet und können ohne Neuallokierung für neue Daten verwendet werden.

Es gibt aber auch Fälle, in denen man möglichst alle Extents freigeben möchte um Speicherplatz zu sparen. Für diese Fälle bietet Oracle Database 11.2.0.2 eine neue Syntax: TRUNCATE TABLE ... DROP ALL STORAGE, wie das Beispiel zeigt:

create table wdg (nr number,text varchar2(100))
/
begin
 for i in 1..3000 loop
  insert into wdg values (i,'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq');
  commit;
 end loop;
end;
/
select * from dba_extents where segment_name='WDG'
/


OWNER  SEGMEN PARTITION_NAME SEGMENT TABLESP  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------ -------------- ------- ------- ---------- ---------- ---------- ---------- ---------- ------------
TT     WDG                   TABLE   USERS            0          4        488      65536  8            4
TT     WDG                   TABLE   USERS            1          4        496      65536  8            4
TT     WDG                   TABLE   USERS            2          4        504      65536  8            4
TT     WDG                   TABLE   USERS            3          4        512      65536  8            4
TT     WDG                   TABLE   USERS            4          4        520      65536  8            4
TT     WDG                   TABLE   USERS            5          4        528      65536  8            4

6 rows selected.


truncate table wdg drop all storage

/
select * from dba_extents where segment_name='WDG'
/

no rows selected

Beim Einfügen neuer Daten werden notwendige Extents neu allokiert. Die neue Syntax funktioniert für normale Tabellen und Partitionen, jedoch  nicht für Cluster-Tabellen: Ein TRUNCATE CLUSTER ... DROP ALL STORAGE  funktioniert demnach nicht!

Im Falle der Partitionen wird das TRUNCATE im Rahmen des ALTER TABLE Kommandos angewendet:

alter table wdg truncate partition p1 drop all storage
/

Zurück zur Tippübersicht

Zurück zur Tippübersicht