Partial Indexes für partitionierte Tabellen

Dani Schnider hat zuletzt eine dreiteilige Serie zur Verwendung partieller Indizes veröffentlicht. Dabei stellt sich als erstes die Frage: was ist ein Partial Index überhaupt? Die Antwort lautet: Partielle Indizes werden nur auf einer Teilmenge der Partitionen einer partitionierten Tabelle erzeugt: entweder, um die Ladeprozesse für aktive Partitionen nicht zu beeinträchtigen - in diesem Fall verzichtet man auf eine Indizierung der aktuellen Daten; oder um umgekehrt die Zugriffe auf die aktuellen Daten durch Indizes zu unterstützen, während das für historische Daten vermeidbar ist:

• Partial Indexes Trilogy – Part 1: Local Partial Indexes: erläutert, dass die partielle Indizierung über die Schlüsselwörter INDEXING ON/OFF gesteuert wird, die bestimmen, ob für eine Partition lokale Indizes angelegt werden. In dba_tab_partitions zeigt die Spalte INDEXING an, welche Definition für eine Partition gewählt wurde. Bei der Anlage eines lokalen Index kann man nun die Option INDEXING PARTIAL angeben, die dafür sorgt, dass für alle Partitionen mit indexing=off Index-Partitionen im Zustand UNUSABLE erzeugt werden, zu denen keine physikalischen Segmente angelegt werden. Ohne die INDEXING PARTIAL Option wird der Index in allen Partitionen erzeugt, die indexing Definition ist dann nicht wirksam, so dass man die partielle Indizierung Index-spezifisch einrichten kann. Ein Index rebuild für lokale Indizes ist nur auf Partitionsebene möglich, aber damit kann man dann auch die initial als unusable definierten Partitionen über einen rebuild usable machen - und damit den Index von einem partiellen in einen ganz normalen lokalen Index umwandeln. Eigentlich sind partielle Indizes erst in 12c verfügbar geworden, aber in 11g kann man das Verhalten leicht nachbauen, wenn man einen lokalen Index initial als unusable definiert und dann nur die Partitionen über rebuild verfügbar macht, für die man den Index tatsächlich verwenden möchte.
• Partial Indexes Trilogy – Part 2: Global Partial Indexes: während sich das Verhalten partieller lokaler Indizes in 11g manuell nachbilden lässt, ist das im Fall globaler Indizes nicht der Fall. Bei diesen Indizes werden nur die rowids indiziert, die auf Partitionen verweisen, für die indexing=on gewählt wurde. Die häufigste Ursache dafür, dass man überhaupt einen global Index definiert, ist die Notwendigkeit, einen unique index zu erzeugen, der den partition key nicht enthält - und das ist dann mit einem partial index aus naheliegenden Gründen nicht möglich: wenn nicht alle Datensätze indiziert sind, lässt sich die Eindeutigkeit eindeutig nicht garantieren. Mit einem Befehl ALTER TABLE ... MODIFY PARTITION ... INDEXING ON; kann man zusätzliche Partitionen in den global partial index aufnehmen, was aber natürlich massive Index-Maintenance nach sich ziehen kann. Umgekehrt ist die Umstellung auf INDEXING OFF zunächst eine Metadaten-Operation: die Einträge werden nicht sofort aus der Index-Struktur gelöscht, stattdessen werden in dba_indizes orphaned_entries angezeigt, die darauf hin deuten, dass die Index-Struktur temporär nutzlose Elemente enthält. Die Beseitgung dieser Einträge erfolgt schließlich durch "asynchronous global index maintenance" und in diesem Zusammenhang verweist der Herrn Schnider auf einige Artikel von Richard Foote, die ich hier sicher auch schon mal verlinkt habe. Ein anderer interessanter Aspekt ist, dass die Löschung einer Partition mit indexing=off dazu führt, dass der global partial index unusable wird, sofern dabei nicht die UPDATE INDEXES Klausel verwendet wird.
• Partial Indexes Trilogy – Part 3: Queries on Partial Indexes: erläutert, wie sich die Zugriffe über partial indexes verhalten. Im Fall lokaler partial indexes wird der Zugriff über UNION ALL verknüpft: für die indizierten Partitionen erscheint der Index-Zugriff, für die übrigen Partitionen ein Full Table Scan. Abhängig davon, ob indizierte und nicht-indizierte Partitionen oder nur die einen oder die anderen abgefragt werden, ergeben sich unterschiedliche Plan-Varianten, wobei das UNION ALL in allen Fällen erscheint und die irrelvanten Teile des Plans dann ggf. nicht ausgeführt werden müssen. Ähnlich sieht es für die global partial indexes aus, wobei sich weitere Varianten in der Plandarstellung ergeben. Auch im Rahmen der Star Transformation im Data Warehouse können partielle Indizes verwendet werden.