Alter Table Shrink

Mit dem Kommando ALTER TABLE table_name SHRINK SPACE; kann man den Speicherplatzbedarf einer Tabelle verringern. Zu den Voraussetzungen dieses Features gehört die Aktivierung des row movements, also der Möglichkeit, eine row an einen anderen Speicherort zu verschieben. Was es damit auf sich hat, soll folgender Test verdeutlichen:

Zunächst lege ich eine harmlose Tabelle an:

SQL> r
  1  create table t1
  2  as
  3  select rownum col1
  4    from dual
  5* connect by level <= 100000

Tabelle wurde erstellt.

Tom Kytes ShowSpace-Prozedur liefert dafür folgende Angaben:

SQL> exec show_space('T1')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................              76
Total Blocks............................             128
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              45
Unused Bytes............................         737,280
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             709
Last Used Block.........................              19

PL/SQL-Prozedur erfolgreich abgeschlossen.

Anschließend komprimiere ich die Tabelle mit Hilfe des SHRINK SPACE Kommandos:

SQL> alter table t1 enable row movement;
Tabelle wurde geändert.
SQL> alter table t1 shrink space;
Tabelle wurde geändert.

Und erhalte folgende Statistiken:

SQL> exec show_space('T1')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................              76
Total Blocks............................              84
Total Bytes.............................       1,376,256
Total MBytes............................               1
Unused Blocks...........................               1
Unused Bytes............................          16,384
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             709
Last Used Block.........................              19

PL/SQL-Prozedur erfolgreich abgeschlossen.

Die nicht verwendeten Blocks werden also freigegeben, statt 128 Blocks werden nur noch 84 Blocks verwendet. An den gefüllten Blocks ändert sich nichts.

Im nächsten Schritt lösche ich 80% der Zeilen aus der Tabelle.

SQL> delete from t1 where col1 <= 80000;
80000 Zeilen wurden gelöscht.
SQL> commit;
Transaktion mit COMMIT abgeschlossen.

An der Anzahl der verwendeten Blocks ändert sich dadurch nichts, durchaus aber am Füllgrad der Blocks

SQL> exec show_space('T1')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              60
Full Blocks        .....................              15
Total Blocks............................              84
Total Bytes.............................       1,376,256
Total MBytes............................               1
Unused Blocks...........................               1
Unused Bytes............................          16,384
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             709
Last Used Block.........................              19

PL/SQL-Prozedur erfolgreich abgeschlossen.

Ein anschließendes SHRINK für die Tabelle führt dann zum Reorg und zu einer Verkleinerung der Tabelle:

SQL> alter table t1 shrink space;
Tabelle wurde geändert.
Abgelaufen: 00:00:05.62

SQL> exec show_space('T1')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               1
Full Blocks        .....................              15
Total Blocks............................              20
Total Bytes.............................         327,680
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             573
Last Used Block.........................               4

PL/SQL-Prozedur erfolgreich abgeschlossen.

Deutlich wird auch, dass die physikalische Reorganisation Ressourcen kostet - im harmlosen Beispielfall immerhin bereits mehr als fünf Sekunden.

Ein hübsches Hilfsmittel für die Analyse ist auch das dbms_rowid-Package, mit dem man die Zuordnung der Sätze zu den Blocks detailliert bestimmen kann:

SQL> r
  1  select blockid, count(*)
  2    from (select dbms_rowid.rowid_block_number(rowid) blockid
  3            from t1)
  4   group by blockid
  5*  order by blockid

   BLOCKID   COUNT(*)
---------- ----------
       560       1328
       561       1328
       562       1328
       563       1328
       564       1328
       565       1328
       566       1328
       567       1328
       568       1328
       569       1328
       570       1328
       571       1328
       572       1328
       574       1328
       575       1328
       576         80

16 Zeilen ausgewählt.