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.

Partitioning

In seinem Blog erläutert Greg Rahn, wozu Partitionierung gut ist. Neben den administartiven Vorteilen betont er dabei auch die positiven Performance-Effekte. Vor einiger Zeit hatte Martin Widlake in einer Serie von Blog-Einträgen die provokative Formulierung "Partitions are Not for Performance" gewählt und ein paar Begründungen für seine Meinung geliefert (wobei es ihm wohl in erster Linie darum ging, die administrativen Vorteile stärker in den Vordergrund zu stellen). Ich halte die Performance-Effekte jedenfalls für den entscheidensten Vorteil der Partitionierung (was aber daran liegen könnte, dass ich nur noch selten administriere).

Table compression

Greg Rahn hat eine hübsche Zusammenfassung zum Thema table compression veröffentlicht. Neu war mir die OLTP compression, die in 11.1 dazugekommen ist.

Grouping

Von Rob von Wijk gibt's eine Präsentation mit dem ausgesprochen ambitionierten Titel All About Grouping. Nach Lektüre des Texts denke ich, dass da tatsächlich alles drin steht, was man zum Thema wissen sollte.

Der (falls es dieses Wort geben sollte) erinnerungswürdigste Punkt dabei ist, dass die elaborierteren Gruppierungsfunktionen (Rollup, Cube) ein Ergebnis liefern, das auch als UNION ALL mehrerer gruppierender Queries ermittelt werden könnte (die dann jeweils unterschiedliche Hierarchieebenen repräsentieren).

DB_FILE_MULTIBLOCK_READ_COUNT revisited

Zuletzt habe ich diverse interessante Artikel zum Thema DB_FILE_MULTIBLOCK_READ_COUNT gelesen, darunter Kerry Osbornes schöne Zusammenfassung, die noch mal die zentralen Punkte anspricht:

• ab 10.2 sollte man den DB_FILE_MULTIBLOCK_READ_COUNT nicht mehr setzen
• ohne Erfassung von workload Statistiken unterscheidet Oracle intern zwischen einem MBRC für die Berechnung der Kosten und einem für die tatsächliche Durchführung von multiblock reads. Diese unterschiedlichen Werte werden über hidden parameters abgebildet. Setzt man den MBRC aber explizit, werden beide Werte überschrieben, was ziemlich sinnlos ist.
• mit workload statistics verwendet Oracle einen ermittelten Durchschnittswert und ignoriert den Initialisierungsparameter (weitgehend).

Im angesprochenen Blog gibt's auch noch diverse Links (auf Blogs von Jonathan Lewis und Randolf Geist); Richard Foote hat übrigens auch vor kurzem über das Thema geschrieben und bei Gelegenheit ergänze ich den Link.

CTAS für MS SQL

Noch ein Eintrag für mein kleines Wörterbuch Oracle - MS SQL. In Oracle ist CTAS (also ''CREATE TABLE AS SELECT'') eine meiner Lieblingsoperationen:

SQL> create table my_dual
  2  as
  3  select *
  4    from dual;

Tabelle wurde erstellt.

Für den SQL Server kann man die Anlage einer Tabelle aus den Ergebnissen einer Abfrage über folgendes Konstrukt erreichen:

select *
  into my_dual
  from dual

Sequenzgenerator

in der Reihe "Wie sag ich's dem SQL Server?" hier ein Beitrag zum Thema Sequenzgenerierung:

Oracle:

SQL> select rownum
  2    from dual
  3  connect by level <= 10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

9 Zeilen ausgewählt.

MS SQL

With NumberSequence 
as
( Select 1 as Number
   union all
  Select Number + 1
    from NumberSequence
   where Number < 10
 )
Select * From NumberSequence

1
2
3
4
5
6
7
8
9
10

Wie bei den hierarchischen Queries verwendet Microsoft also auch hier ein rekursives Verfahren, das zwar etwas umständlicher ist als die Oracle-Lösung, aber durchaus nachvollziehbar. Eine ausführlichere Erläuterung zum Thema findet sich hier.

Aktuelles Datum

Da ich immer mal wieder danach suche, hier ein paar Queries, um das aktuelle Datum in unterschiedlichen RDBMS zu ermitteln:

-- Oracle
select sysdate
  from dual;

-- DB2
select current date 
  from sysibm.sysdummy1;

-- MS SQL
SELECT GETDATE();

Ein ambitionierterer Eintrag könnte jetzt der Frage nachgehen, warum Oracle und DB2 eine Dummy-Tabelle einsetzen und der SQL Server darauf verzichtet, aber das schenke ich mir für diesmal. Ursprünglich wollte ich den Eintrag ''Stein von Rosette'' nennen, nehme aber an, dass er auch dazu zu kurz wäre...

Bitmap- und B-Tree-Indizes

Jonathan Lewis erläutert in seinem Blog ein paar grundsätzliche Eigenschaften von Bitmap- und B-Tree-Indizes. Nichts, was nicht auch anderswo zu finden wäre, aber wie üblich äußerst präzise.