Insert-Sortierung und Index-Größe

Mal wieder ein Titel, der den Eindruck erweckt, dass vielleicht eine größere Präzisierung möglich gewesen wäre - aber da Richard Foote dem Artikel, den ich hier wiedergebe, den Titel Why A Brand New Index Might Benefit From An Immediate Coalesce (One Slip) gegeben hat, fühle ich mich nicht dazu verpflichtet, länger darüber nachzudenken. Worum es geht, ist Folgendes: der Herr Foote zeigt in seinem Blog, dass ein Index auf einer via Insert as Select befüllten Tabelle deutlich größer ist als erforderlich. Die Reihenfolge im Testfall ist dabei:

• Anlage einer Testtabelle mit Index (non-unique) und 1M rows.
• der Index umfasst 2226 leaf blocks.
• Sicherung von 30% der Daten (also 300K rows) in einer Hilfstabelle und Durchführung eines truncate auf der Quelltabelle.
• der Index enthält jetzt keine Einträge.
• Insert der in der Hilfstabelle gesicherten Datensätze in die Ursprungstabelle.
• der Index umfasst 1112 leaf blocks. Also recht genau 50% der initialen Größe.

Die Frage ist somit: warum ist der Index nicht auf 30% der Ursprungsgröße geschrumpft? Ein Blick auf die Index-Struktur zeigt, dass sehr viele Blocks jetzt nur zu 50% gefüllt sind und nicht mehr zu 90%. Ursache sind 50-50-block splits in der Index-Struktur, die sich ergeben, weil die Daten in der Hilfstabelle nicht sortiert vorlagen, sondern (mehr oder minder) willkürlich in freien Blocks untergebracht wurden: der Transfer dieser Sätze in die Ursprungstabelle fügt also nicht nur am Ende der Indexstruktur neue Einträge ein (was zu 90-10-block splits führen würde), sondern ergänzt Einträge mitten in den bereits angelegten Strukturen, was zur 50-50-Teilung der Index-Blocks führt. Um eine kompakte Index-Struktur zu erhalten, wäre eine Sortierung beim Insert as Select notwendig - oder die Verwendung eines Append-Hints, der eine Vorsortierung der eizufügenden Sätze durchführt. Dabei gilt natürlich, dass ein extrem kompakter Index nur unter bestimmten Umständen sinnvoll ist: entweder, wenn die Tabelle statisch ist (also überhaupt keine DML-Operationen erfolgen), oder wenn nur am Ende des Index eingefügt wird und somit in der Folge nur Splits vom Typ 90-10 erfolgen.