Angemessene Indexgröße

Für All Things Oracle hat Jonathan Lewis vor einigen Wochen einen Artikel geschrieben, in dem er erläutert, wie man die angemessene Größe eines B*Tree Index bestimmen kann. Darin geht er davon aus, dass ein stabiler B*Tree Index in der Regel etwa 70% des verfügbaren Speichers in den Leaf-Blocks auslastet, während etwa 30% freier Speicher in den Leaf-Blocks verfügbar bleibt. Unter dieser Voraussetzung ist es möglich eine grobe Abschätzung zu geben, wie groß ein Index in etwa sein sollte - und daraus kann man dann ableiten, ob unter Umständen einer der seltenen Fälle vorliegt, in denen ein Index-Rebuild tatsächlich einmal eine gute Idee sein könnte. Seine Formel zur Berechnung der angemessenen Indexgröße enthält folgende Elemente:

• sum(user_tab_cols.avg_col_len + 1) für alle Index-Spalten
  
• 6 byte für die rowid (8 byte im Fall eines globally partitioned index)
• 1 byte, wenn der Index non-unique ist
• 2 byte pro row
• 2 byte pro row directory Eintrag

Der im Leaf-Block verfügbare Platz wird berechnet als: Blöckgröße - 200 Byte (für das block directory) und muss den pctfree-Wert berücksichtigen. Damit kann man dann berechnen, wie viele Index-Einträge in einen Leaf-Block passen und wie groß der Index insgesamt sein muss. Die Formel sollte für nicht komprimierte Indizes nicht mehr als 1 oder 2% von der tatsächlichen Index-Größe nach einem rebuild abweichen - und auf seiner Webseite hat der Herr Lewis auch ein entsprechendes Skript bereitgestellt.

Ein klassischer Fall, in dem ein Index von einem rebuild profitieren kann, ist ein fifo (first in first out) Processing, bei der einzelne Einträge stehen bleiben - und da ein Index-Block erst wieder verfügbar wird, wenn alle Einträge daraus gelöscht wurden, machen solche Überbleibsel Probleme. Solche Fälle kann man recht komfortabel mit dem dbms_space-Package analysieren und auch zu diesem Zweck gibt es ein Skript beim Herrn Lewis.

Ein weiteres Hilfsmittel zur Analyse von Indizes ist der Treedump den man über ein trace event erstellen lassen kann. Das Vorgehen dabei und einige grundlegende Analysetechniken (etwa die Verwendung von grep) werden im Artikel erläutert, aber in diesem Fall spare ich mir die detaillierte Nacherzählung. Festzuhalten bleibt, dass der Treedump eine genauere Analyse der Index-Struktur erlaubt und es möglich macht, ungewöhnliche Fälle exakter zu bestimmen.