Donnerstag, Juli 21, 2016

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.

Montag, Juli 18, 2016

Hint-Verwendung zur Bestimmung der Join-Reihenfolge

Brendan Furey erläuert in seinem Blog die Möglichkeiten, die Oracle bietet, die Join-Reihenfolge über Hints zu beeinflussen. Darin weist er zunächst darauf hin, dass der USE_HASH Hint eigentlich nur einen Parameter benötigt und nicht die Angabe der beiden Aliase der Tabellen, die man miteinander verknüpfen will: bei der Angabe von zwei Aliasen betrachtet Oracle dies als zwei Hints und wird folglich einen der beiden übergehen. Welcher der Hints nicht berücksichtigt wird, hängt von der Reihenfolge ab, in der die Tabellen gejoint werden, und in diesem Zusammenhang wird auf einen klassischen "Quiz Night" Artikel von Jonathan Lewis verwiesen, der erläutert, dass im Fall eines 4-Tabellen-Joins, bei dem die Join-Reihenfolge über LEADING Hints und die Join Methode über USE_HASH Hints bestimmt ist, immer noch acht unterschiedliche Pläne entstehen können, weil dadurch noch nicht festgelegt ist, welche rowsource als "build table" und welche als "probe table" zu betrachten ist, was über die Hints SWAP_JOIN_INPUTS und NO_SWAP_JOIN_INPUTS gesteuert werden kann. An dieser Stelle ergänzt der Herr Furey seine Überlegung, dass in diesem Fall eigentlich zwei Ebenen von Sortierungen zu berücksichtigen sind, da man den gleichen Plan mit mehreren unterschiedlichen Hint-Kombinationen hervorrufen kann: eine Outer-level join order (die die Reihenfolge angibt, in der die Tabellen in einem komplexen Join verknüpft werden) und eine Inner-level join order (die anzeigt, auf welcher Seite eine Joins eine Tabelle bei der Verknüpfung mit einer anderen rowsource eingesetzt wird: also im Hash Join etwa als build oder probe table). Nach dieser Definition hat der LEADING Hint unterschiedliche Auswirkungen, abhängig davon, ob ein Hash Join oder ein anderer Join Typ im Spiel ist. Ich denke, das ist eine interessante Art, den Sachverhalt zu betrachten, obwohl ich mir noch nicht ganz sicher bin, ob diese Unterscheidung massive Vorteile bei der Beschreibung der Situation bringt.

Montag, Juli 11, 2016

Staleness IMPORT für Materialized Views in all_mviews nach data pump Import

Eine kurze Notiz, da ich bei meiner Recherche im Netz nicht arg viel zum Thema gefunden habe: durch den Transport einer Materialized View über data pump (expdp - impdp) werden zwar die Daten des Segments transportiert, aber die MView wird nicht mehr für Query Rewrite verwendet und auch ein Fast Refresh ist nicht mehr möglich und scheitert an einem Fehler ORA-12034:
ORA-12034: materialized view log on "XXX"."YYY" younger than last refresh
An dieser Stelle ist daher ein complete refresh erforderlich. Insofern ist es vermutlich sinnvoller, auf den Transfer der MView-Daten komplett zu verzichten und sie lieber gleich im Zielsystem neu aufzubauen. Besonders überraschend ist das nicht - auch wenn sich dafür vermutlich eine Lösung bei der Implementierung hätte finden lassen, wenn man sich darum bemüht hätte -, aber falls ich es schon mal wusste, habe ich es vergessen. Überraschender ist vielleicht der Status IMPORT, der in diesem Fall für die MView in der Spalte STALENESS in DBA_MVIEWS auftaucht - obwohl die Dokumentation diesen Status nicht erwähnt; dort findet man nur den Hinweis auf die ergänzende Spalte UNKNOWN_IMPORT: "Indicates whether the materialized view is imported (Y) or not (N)". Gänzlich undokumentiert ist dieser Staleness-Status aber nicht: man findet ihn im MOS-Dokument "How to Perform an EXPDP/IMPDP for a SCHEMA with a MATERIALIZED VIEW. Highlighting the IMPORT Staleness and UNKNOWN_IMPORT column in DBA_MVIEWS (Doc ID 1579866.1)" und dort wird das Verhalten auch noch mal anhand eines Beispiels dargestellt.

Montag, Juli 04, 2016

Default für Lead und Lag Funktionen

Eine Lösung für ein kleines Problem, dem ich mit den analytischen Funktionen LEAD und LAG schon häufiger begegnet bin, findet man bei Connor McDonald: an den Grenzen eines durch die analytischen Funktionen definierten Fensters erhält man üblicherweise einen NULL-Wert - also etwa dann, wenn man nach dem Vorgängerwert für den ersten Satz eines Ergebnisses sucht. Diesen Fall kann man mit einer NVL-Funktion abfangen, aber eleganter und kürzer ist die Verwendung eines default-Wertes, den die analytische Funktion als dritten Parameter (nach value_expr und offset) annimmt:

create table t
as
select rownum id
  from dual
connect by level <= 10;

select id
     , lag(id) over(order by id) lag_id1
     , nvl(lag(id) over(order by id), 0) lag_id2
     , lag(id, 1, 0) over(order by id) lag_id3
  from t;

ID    LAG_ID1    LAG_ID2    LAG_ID3
-- ---------- ---------- ----------
 1                     0          0
 2          1          1          1
 3          2          2          2
 4          3          3          3
 5          4          4          4
 6          5          5          5
 7          6          6          6
 8          7          7          7
 9          8          8          8
10          9          9          9

Ich vergesse auch die offset-Option gerne und komme mal wieder zum Ergebnis, dass es gelegentlich noch mal nützlich wäre, die Dokumentation gründlich zu lesen.

Dienstag, Juni 28, 2016

Index Sanity Check für Cardinality Schätzungen

Auf das Thema weise ich in den OTN-Foren recht häufig hin und da ist es gut, dass Jonathan Lewis einen Artikel dazu geschrieben hat, auf den man verweisen kann: die Löschung eines Index kann den Ausführungsplan einer Query auch dann ändern, wenn der Index gar nicht im Execution Plan erscheint. Die Erklärung dafür sind Sanity Checks die auf der Basis der Index-Statistiken für mehrspaltige Indizes operieren. Im Artikel stellt Jonathan Lewis ein kleines Beispiel vor, das diesen Sachverhalt deutlich macht: darin wird ein Index gelöscht, der im Ausführungsplan keine Rolle spielt. Dieser Index ist auf einer Kombination von drei Spalten angelegt, von denen die erste 10 distinkte Werte enthält, die zweite nur einen und die dritte wiederum 20 distinkte Werte. Die Kombination der Selektivitäten würde - da der Oracle Optimizer bekanntlich von unabhängigen Wahrscheinlichkeiten ausgeht, also keine Abhängigkeiten annimmt - daraus 10 * 1 * 20 = 200 machen. In den Index-Statistiken ist aber festgehalten, dass die Zahl der tatsächlich auftretenden Kombinationen nur bei 20 liegt und dieser Wert wird dann auch vom Optimizer berücksichtigt. Die Löschung des Index führt dann natürlich zum Verlust der Information und zu einer weniger akkuraten Schätzung. Seit 11g kann man in einem solchen Fall den Informationsverlust durch Anlage von Extended Statistics auf der Spaltengruppe vermeiden, aber grundsätzlich zeigt das Beispiel, dass man beim Löschen "unbenutzter" Indizes vorsichtig sein sollte.