Neue Index-Features in 12.2

Richard Foote hat angekündigt, einige Artikel zu Index-Features, die in 12.2 ergänzt wurden, zu veröffentlichen. Ich werde versuchen, diese Artikel hier zusammenzufassen:

• Oracle Database 12c Release 2: New Indexing Features – Long Identifiers (Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict): die Länge des Titels steht in Zusammenhang mit dem vorgestellten Feature - die Größenbegrenzung für Identifier wurde angehoben: statt 30 Zeichen kann ein Indexname jetzt 128 Zeichen umfassen. Klingt für mich eher bedrohlich, obwohl es natürlich Fälle gibt, in denen das alte Limit ein Problem darstellte.
• 12.2 Index Advanced Compression “High” – Part I (High Hopes): die neue "high" Variante der in 12.1 eingeführten "index advanced compression" (bei der mich immer noch die Reihenfolge der Namensbestandteile stört) verspricht eine deutlich verbesserte Komprimierung und verwendet andere Algorithmen als die "low" Variante, die nur auf der Deduplizierung von Index-Einträgen im Leaf Block basierte. Bei der "high" Variante werden die Index-Einträge in compression units gespeichert, wobei das Verfahren offenbar dem der hybrid columnar compression ähnelt. Im Beispiel sind die Effekte recht eindrucksvoll: während normale index compression den Beispiel-Index (in dem 25% der Daten in einem bestimmten Teilbereich der Tabelle Duplikate sind)  größer macht als die nicht komprimierte Variante (2158 Blocks vs. 2684 Blocks), sorgt die "low" Variante für eine geringfügige Verkleinerung (2057 Blocks), aber die "high" Variante macht den Index deutlich kompakter (815 Blocks; wobei aufgrund eines Bugs eine Neuerfassung der Statistiken erforderlich ist, die zunächst 0 Blocks anzeigen).
• 12.2 Index Advanced Compression “High” Part II (One Of My Turns): untersucht das Verhalten mit einem Index auf eindeutigen Werten ohne Wiederholungen. Für die normale index compression und die "low" Variante der index advanced compression ergibt sich jeweils der Fehler "ORA-25193: cannot use COMPRESS option for a single column key". Die "high" Variante hingegen reduziert die Indexgröße im Test wiederum signifikant (2088 Blocks vs. 985 Blocks). Auf Wunsch von Jonathan Lewis hat der Herr Foote das Beispiel mit größeren Id-Werten und einer randomisierten Verteilung wiederholt, wodurch sich der Effekt verkleinert, aber immer noch eine Größenreduzierung um 30% hervorruft. Jonathan Lewis hat dem Thema auch noch einen eigenen Artikel in seinem Scratchpad gewidmet.
• 12.2 Index Advanced Compression “High” Part III (I Can’t Give Everything Away): zeigt, dass die Vererbung der compression-Attribute über db_index_compression_inheritance gesteuert werden kann. Außerdem kann man für einen Tablespace einen Default-Wert angeben.

Ich versuche, wie üblich, die in der Serie folgenden Artikel nachzutragen.

Attribut-Clustering und ein Jubiläum

Vor kurzem hat Connor McDonald eine Artikelserie zum Attribut-Clustering in 12c veröffentlicht, die im dritten Teil ein ziemlich seltsames Verhalten demonstriert. Ein Kommando:

alter table source_data add clustering by linear order(object_id);

führt zu einem erfolgreichen Clustering, während das Kommando:

alter table source_data clustering by linear order(object_id);

erfolgreich abläuft, aber anscheinend keinerlei Wirkung hat: das "add" hat also die entscheidende Wirkung. Randolf Geist hat in seinem Kommentar zum Artikel die naheliegende Frage nach dem Sinn der zweiten Variante gefragt, was Connor McDonald leider nicht beantworten konnte. Seltsam ist das auf jeden Fall.

Einen anderen Punkt will ich auch noch erwähnen: dies ist der tausendste Eintrag in diesem Blog und ich nehme das mal wieder zum Anlass, mich bei meinen Lesern zu bedanken. Darüber hinaus ergänze ich aus kalendarischen Gründen dann auch noch alle jahreszeitüblichen Grüße und Wünsche.

Datenarchivierungs-Strategien in Oracle (Delete/Insert)

Jonathan Lewis hat vor kurzem zwei interessante Artikel zur Archivierung von Daten veröffentlicht, wobei die Anforderung so definiert ist, dass Daten via delete aus einer Tabelle gelöscht und die gleichen Datensätze per insert in eine zweite Tabelle eingefügt werden:

• Delete/Insert: erläutert das grundsätzliche Problem: wie kann sichergestellt werden, dass die gleichen Daten, die aus der ersten Tabelle (t1) gelöscht wurden auch in der zweiten Tabelle (t2) eingefügt werden. Relativ einfach ist diese Anforderung mit massiven Locks zu erreichen, mit denen man konkurrierende DML-Operationen verhindert, aber in der Regel ist das keine wünschenswerte Verhaltensweise in einem multi-user System. Im Artikel werden mehrere möglich Strategien - samt ihrer jeweiligen Vor- und Nachteile - erläutert, darunter: (1) die Sammlung der vom delete betroffenen Sätze in einer collection, für die man dann ein insert in einer forall Schleife durchführt, (2) Sammlung der betroffenen rowid-s über ein select for update gefolgt von einem delete und einem insert mit den ermittelten rowid-s. In den Kommentaren zum Artikel werden weitere Optionen diskutiert (Trigger; logische Löschung mit delete Flag - unter Umständen als invisible column; Verwendung eines table lock; Verwendung einer GTT in Kombination mit einer pipelined table function).
• Delete/Insert #2: analysiert eine in einem der Kommantare zum ersten Artikel aufgeführte Idee, die Jonathan Lewis zunächst verwarf und dann noch mal genauer untersuchte: die Verwendung einer "as of SCN" Klausel, die sich unter bestimmten Umständen ebenfalls verwenden lässt.

Sehr viel kompakter als alle Oracle-Varianten ist die postgres-Lösung zum Thema - mit Verwendung einer CTE und einer returning clause, die insert und delete zusammenfasst und die ich hier gelegentlich schon mal erwähnt hatte. Nachdem diese Syntax auch in den Kommentaren zum ersten Artikel erwähnt wurde und vom Herrn Lewis gelobt wurde ("That Postgres syntax is wonderfully compact – much more convenient than the PL/SQL of option 1 which is probably the closest you could get to it in Oracle.") habe ich eine entsprechende Idee im OTN Forum "database ideas" angelegt.

Bestimmung der Verwendung von Indizes in 12.2

Bis Oracle 12.2 zum Download verfügbar ist, werde ich mich damit wohl nur theoretisch beschäftigen - also durch die Lektüre von Artikeln zum Thema. Eine der besten Quellen solcher Artikel ist derzeit der Blog von dbi Services - insbesondere, wenn der zugehörige Autor Franck Pachot heißt. Einer dieser Artikel behandelt das in 12.2 eingeführte erweiterte Monitoring zur Index-Verwendung und liefert unter anderem folgende Informationen:

• die View dba_index_usage liefert kumulierte Nutzungsstatistiken für jeden Index.
• in v$index_usage_info finden sich Nutzungsstatistiken seit dem letzten Flush (der alle 15 min stattfindet).
• üblicherweise basieren die Angaben auf Sampling, können bei Bedarf auch für jede Ausführung erfasst werden (was natürlich einen Performance-Overhead hervorruft).
• auch die Verwendung eines Index im Rahmen der gather_%_stats-Aufrufe werden erfasst, so dass die Angaben nur begrenzt Auskunft darüber geben, ob ein Index für Anwendungs-Zugriffe relevant ist.
• die Verwendung eines Index auf einem Foreign Key zur Vermeidung eines table locks (Stichwort: deadlock-Ursache Nr. 1) wird hingegen in den Statistiken nicht erfasst.

Zusammenfassend lässt sich feststellen, dass dieses Monitoring ein Schritt in die richtige Richtung ist, aber nicht ausreicht, um die klassische Frage "kann ich diesen Index gefahrlos löschen?" zu beantworten. Zumal das Thema der Verwendung von Index-Statistiken für den Optimizer ohnehin nicht betroffen ist - aber das kann man dem Monitoring aus meiner Sicht nicht ernsthaft vorwerfen.

Änderung der endpoint value Berechnung für Histogramme seit Oracle 11.2.0.4

Jonathan Lewis und vor ihm bereits Franck Pachot haben zuletzt darauf hingewiesen, dass in 11.2.0.4 eine Änderung der Berechnung der endpoint values für Histogramme von char und nchar (nicht aber varchar2 und nvarchar2) Spalten eingeführt wurde, die eine Neuerzeugung entsprechender Histogramme nach einem Upgrade von einer früheren Version erforderlich macht, da sich sonst recht bizarre costing-Effekte ergeben.

In Francks Test existiert eine Spalte mit den beiden Werten 'Y' und 'N', die massive ungleichmäßig verteilt sind (100K zu 1K). Zu erwarten wäre, dass diese Verteilung durch ein Frequency-Histogramm exakt abgebildet wird, was in 11.2.0.3 auch der Fall ist. Nach dem Upgrade auf 11.2.0.4 (ohne Neuerzugung des Histogramms) wird die Cardinality für beide Werte aber mit der Formel für nicht im frequency Histogramm vorliegende Werte berechnet: also "Häufigkeit des seltensten Werts im Histogramm" geteilt durch zwei (im Beispiel also 500). Demnach scheinen nach dem Upgrade also alle Werte unbekannt zu sein. Verantwortlich dafür ist die interne Ablage der endpoint values im Histogramm: bis 11.2.0.3 erfolgte das Padding der Werte mit spaces (ASCII 0x20), in 11.2.0.4 erfolgt es mit Nullen (ASCII 0x00) - was dem Verhalten entspricht das scon ihn älteren Releases für varchar2 verwendet wurde. Insofern ist die Neuerzeugung der Histogramme unvermeidbar.

Jonathan Lewis erwähnt in seinem Artikel auch noch mal die wichtige Tatsache, dass die Erzeugung vieler Histogrammtypen in 12c mit einem "approximate NDV" Verfahren erfolgt: die 5500 row samples, die in der Vergangenheit häufig zu Problemen geführt hatten, sind demnach in vielen Fällen nicht mehr im Spiel.