Partitionierung mit Postgres 12

Daniel Westermann hat im dbi Services Blog eine interessante Serie begonnen, die sich mit den aktuellen Möglichkeiten der Partitionierung in Postres 12 (das sich noch im Beta-Status befindet) beschäftigt:

• PostgreSQL partitioning (1): Preparing the data set: beschreibt die Erzeugung einer Testtabelle aus einer öffentlich verfügbaren Datenquelle der US-Regierung. Die Ladeoperation erfolgt über ein copy-Kommando. Zu dieser Tabelle wird eine Materialized View erzeugt. Wenn eine MV einen unique index besitzt kann der Refresh concurrently erfolgen (also parallele Zugriffe während des Refreshs erlauben).
• PostgreSQL partitioning (2): Range partitioning: zeigt das Vorgehen zur Anlage einer Tabelle mit Range-Partitionierung auf Jahresbasis, bei dem zunächst die partitionierte Tabelle und anschließend separat die Partitionen angelegt werden. Die ranges werden in der Definition der Partition explizit angegeben - also nicht über ein Pattern. Außerdem muss die Obergrenze bereits den ersten Tag des Folgejahres angeben, da die "to" Angabe eine exklusives Limit darstellt. Zusätzlich zu den definierten range-Partitionen kann eine default Partition erzeugt werden, die alle Datensätze aufnimmt, die keinem der definierten ranges entsprechen.
• PostgreSQL partitioning (3): List partitioning: zeigt das Verhalten von List Partitionierung, dass anscheinend keine besonderen Überraschungen bietet.
• PostgreSQL partitioning (4): Hash partitioning: beschäftigt sich mit Hash Partitionierung. Dazu wird in der Partitions-Definition eine modulus-Funktion auf einen Spaltenwert verwendet, um die Zuordnung zu bestimmen. In diesem Fall ist die Anlage einer default-Partition nicht möglich (oder sinnvoll). Da die Verteilungsfunktion manuell definiert wird, liegt es beim Verwender, dafür zu sorgen, dass sich eine plausible und gleichmäßige Verteilung auf die Partitionen ergibt. Hier scheint das Verfahren noch nicht allzu elaboriert zu sein.
• PostgreSQL partitioning (5): Partition pruning: behandelt das Partition Pruning, also die Möglichkeit des Optimizers, für eine Abfrage irrelevante Partitionen beim Zugriff ausklammern zu können. In Postgres 10 funktionierte das nur, wenn eine Einschränkung bereits in der Planning Phase bestimmbar war. In Postgres 11 kann das Pruning auch erfolgen, wenn die Einschränkung erst bei der Execution erkennbar wird.
• PostgreSQL partitioning (6): Attaching and detaching partitions: zeigt, wie man Partitionen über attach in eine partitionierte Tabelle eingliedern - bzw. über deatch daraus lösen kann. Die abgehängte Tabelle kann dann nach belieben weiter verwendet werden.
• PostgreSQL partitioning (7): Indexing and constraints: erklärt, wie die Beschränkungen der Partitionierung reduziert werden konnten. In Postgres 10 konnte man keinen Primary Key auf einer partitionierten Tabelle anlegen, was inzwischen möglich ist. Möglich ist auch die Anlage eines Index, der auf eine einzelne Partition beschränkt bleibt. Was noch nicht funktioniert ist die Anlage eines partitionierten Index (in Oracle wäre das ein lokaler Index) mit der Option concurrently. Man kann den Index aber beschränkt auf der Ebene der partitionierten Tabelle anlegen, was ihn in einem invaliden Zustand bringt, und dann ein create index concurrently auf Partitionsebene starten. Anschließend können diese Index-Partitionen an den partitionierten Index attached werden (was diesen in den Zustand valid überführt). Auch die Anpassung von Constraints (etwa not null) kann individuell auf Partitions-Ebene erfolgen.

 Die Serie wird fortgesetzt und ich versuche - wie üblich - die folgenden Artikel hier zu ergänzen.

Performance von lokalen und globalen Indizes partitionierter Tabellen

Richard Foote hat eine neue Artikel-Serie gestartet, in der er sich mit der Performance lokaler und globaler Indizes für partitionierte Tabellen beschäftigt. Da ich auch zu den Leute gehöre, die globale Indizes weitgehend vermeiden, finde ich Argumente, die für die globalen Indizes sprechen, grundsätzlich interessant:

• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie): liefert ein sehr einfaches initiales Beispiel: eine Tabelle enthält Daten für acht Jahre und einen Index für die Spalte TOTAL_SALES, die für den Wert 42 insgesamt 10 Datensätze liefert. Bei Verwendung einer nicht partitionierten Tabelle erfordert ein Zugriff auf diese 10 Datensätze 14 consistent gets (4 für den Durchgang durch die Indexstruktur und 10 für die Datensätze) - und die gleichen 14 LIOs sind auch erforderlich, wenn man nur einen Datensatz für ein bestimmtes Jahr auswählt, da die Datumsangabe nicht Teil des Index ist und daher erst bei der Filterung berücksichtigt wird. Für den globalen Index auf einer partitionierten Tabelle ergeben sich die gleichen 14 LIOs für Fall 1, aber für den zweiten Fall sinkt die Zahl der LIOs auf 5, da hier für den Optimizer klar ist, dass nur die Partition des fraglichen Jahres den gewünschten Datensatz enthalten kann - es erfolgt also ein "partition pruning".
• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane): bis zu Oracle 8 bestand die rowid grundsätzlich aus 6 byte und enthielt "file number", "block number" und "row number". Seit der Einführung des Partitioning in Oracle 8 kann ein partitioniertes Objekt in mehreren Tablespaces abgelegt werden, was dazu führte, dass die "file number" nicht mehr eindeutig war. Daher wurde die rowid für globale Indizes um die "data object id" erweitert (und damit auf 10 byte erweitert). Diese zusätzliche Information macht das "partition pruning" möglich, da der Index in jedem einzelnen Schlüssel die dafür erforderliche Information enthält.
• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet): weist darauf hin, dass ein lokaler Index durch die kürzere rowid (ohne die "data object id" und daher nur 6 byte lang) etwas kleiner ist als ein entsprechender globaler Index. Für einen Zugriff, der sich auf eine Partition beschränkt, ist der lokale Index daher etwas effizienter als der globale Index (4 vs. 5 LIOs). Beim Partitions-übergreifenden Zugriff ist der globale Index dagegen deutlich effizienter, da sich das Lesen auf eine Index-Struktur beschränkt und nicht über mehrere interne Indizes erfolgen muss.
• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part IV” (Hallo Spaceboy) : zeigt, dass man das BLEVEL eines globalen Index dadurch reduzieren kann, dass man ihn partitioniert (nach einer anderen Spalte als dem partition key).

Wie üblich gilt, dass ich die folgenden Artikel ergänzen werde, wenn ich nicht die Lust daran verliere.

Local Partitioned Indexes mit postgres 11

Daniel Westermann weist darauf hin, das postgres 11 erweiterte Optionen für die Indizierung partitionierter Tabellen liefern. Während in postgres 10 Indizes noch auf Partitionsebene erzeugt werden mussten, kann man sie jetzt für die partitionierte Tabelle definieren, was dazu führt, dass sie automatisch in den untergeordneten Partitionstabellen erzeugt werden. Vielleicht noch interessanter ist die Möglichkeit, primary keys auf partitionierten Tabellen zu erzeugen. Insgesamt ist deutlich zu erkennen, dass die Partitionierung in postgres allmählich ihre Kinderkrankheiten hinter sich lässt.

Nachtrag 26.03.2018: zu den Ergänzungen gehört auch das row movement - also die Verschiebung eines Datensatzes in eine andere Partition bei Änderung des Werts für den partition key. Auch dazu hat der Herr Westermann einen Artikel geschrieben.

Nachtrag 04.04.2018: und noch etwas, das in postgres 11 funktioniert: die postgres-Variante zum Merge-Statement - das "Insert ... on conflict".  Auch dazu hat der Herr Westermann einen Artikel geliefert.

Partitionierungs-Optionen in 12.2

Jonathan Lewis zeigt in seinem jüngsten Artikel die große Flexibilität, die die Definition von partitionierten Tabellen in Oracle 12.2 errreicht hat. Dabei liefert er größeres Code-Beispiel für ein ALTER TABLE ... MOVE, in dem folgende Punkte aufgeführt sind:

• List-Partitionierung über mehrere Spalten.
• automatic: das Schlüsselwort, das die Generierung neuer Partitionen für neu ankommende Daten erlaubt - das entspricht damit dem Interval-Partitioning für Ranges, das man schon aus älteren Releases kannte.
• indexing off: erlaubt die Beschränkung der Indizierung auf einzelne Partitionen und damit die Definition partieller Indizes.
• read only: erlaubt nur lesende Zugriffe für die betroffene Partition.
• including rows where: erlaubt bei einer MOVE-Operation die Verschiebung von Daten auf der Basis eines Filter-Kriteriums.
• online: erlaubt eine online-MOVE-Operation ohne downtime.
• update indexes: aktualisiert Indizes im Rahmen einer MOVE-Operation.
• local: die Indizes werden als lokale Indizes aufgebaut.
• indexing partial: die Indizes werden für die Partitionen mit der Option "indexing off" nicht erzeugt (also ohne Segmente erzeugt und befinden sich daher im Status "unusable").

Dazu gibt es dann noch allerlei Analyse-Code zum Beleg, dass die MOVE-Operation tatsächlich das erwartete Ergebnis brachte, nämlich "Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation." Insgesamt scheint mir das Partitioning ein Bereich zu sein, in dem 12.2 sehr nützliche Ergänzungen liefert und eine sehr große Flexibilität erlaubt.

Online Partitionierung einer existierenden Tabelle in 12.2

Eine sehr schöne Ergänzung der Partitionierungs-Optionen in 12.2 beschreibt Maria Colgan in ihrem Blog: die Möglichkeit, eine nicht partitionierte Tabelle ohne downtime - also online - in eine partitionierte Tabelle umzuwandeln. Die Syntax dazu sieht etwa folgendermaßen aus:

alter table t
partition by ...
(
   partition p1 ...,
   partition p2,
 ...
)
update indexes online

Das sieht für mich sehr intuitiv und vor allem kompakt aus. Dabei dient "update Indexes" wie üblich dazu, die Indizes während des Aktualisierungsvorgangs verfügbar zu halten. Die Default-Optionen bei der Umwandlung der Indizes sind folgende:

• Indizes, die bereits als "global partitioniert" angelegt wurden, behalten ihr Partitionierungs-Schema
• Indizes, die nicht mit dem "partition key" starten, werden "global non-partitioned indexes"
• Indizes, die mit dem "partition key" starten, werden lokal partitionierte Indizes
• Bitmap Indizes werden zu lokal partitionierte Indizes

Dass man diese Default-Optionen überschreiben kann, ist bei Frau Colgan nur implizit angedeutet (nämlich durch die Verwendung des Terminus "default"), aber Richard Foote hat dazu vor kurzem ein Beispiel veröffentlicht: auf das "update indexes" folgen dann in Klammern die Spezifikationen der Konvertierung.

Eine zweite nützliche Ergänzung zur Partitionierung in 12.2 ist die Möglichkeit, die Basis-Tabelle für eine "partition Exchange" Operation mit einem Befehl "create table ... for exchange with table ..." anzulegen. Allerdings scheint dieses Kommando nicht zur automatischen Generierung der passenden lokalen Indizes zu dienen, so dass hier weiterhin eine gewisse Sorgfalt bei der Vorbereitung des partition exchange erforderlich bleibt - was aber insofern kein größeres Problem darstellt, als der Austausch von Partitionen aus meiner Sicht ohnehin ein Task ist, der verskriptet werden sollte.

Darüber hinaus erwähnt die Autorin noch eine dritte nützliche Ergänzung: die Einführung von interval partitioning für List-partitionierte Tabellen. Dabei hoffe ich, dass die Intervall-Partitionierung in 12.2 stabiler geworden ist, als sie das in früheren Releases geworden ist, aber das ist ein Thema, das im Artikel nicht angesprochen wird - und dem im Detail nachzugehen mir aktuell die Zeit fehlt.

Interval-Reference Partitionierung in 12c

Früher einmal habe ich meine Blog-Beiträge selbst erdacht und geschrieben - inzwischen gebe ich sie gerne in Auftrag oder lasse sie in Auftrag geben. So auch hier: in der letzten Woche war Markus Flechtner von Trivadis bei uns im Haus und hat uns in die dunklen Geheimnisse von Oracle 12 eingeweiht. Viele Fragen blieben nicht offen, aber einer meiner Kollegen hatte ein paar Detailfragen zum Thema Interval-Reference-Partitionierung und row movement. Ich hätte es wahrscheinlich bei der Antwort "das ist ein weites Feld" bewenden lassen und vielleicht noch ein paar haltlose Versprechungen gemacht, gelegentlich mal einen Blick darauf zu werfen. Nicht so der Herr Flechtner, der daraus gleich einen Artikel Interval-Reference-Partitionierung: Partition-Merge und Row-Movement gemacht hat. Darin finden sich unter anderem folgende Beobachtungen:

• zur Erinnerung: beim reference partitioning erbt eine child Tabelle die Partitionierungs-Charakteristiken der parent Tabelle. Und beim interval partitioning werden erforderliche Partitionen nach Bedarf auf Basis einer vorgegebenen Ranges-Größe (oder Intervall-Angabe) erzeugt.
• die für die child-Tabelle angelegten Partitionen korrespondieren exakt mit denen der parent-Tabelle: auch die generierten Namen der Partitionen sind identisch.
• ein Merge für Partitionen der parent-Tabelle wird automatisch an die child-Tabelle propagiert. Allerdings sind die Namen auf parent- und child-Ebene dann nicht mehr identisch.
• ein Merge auf child-Ebene ist nicht möglich.
• wie üblich führt die Merge-Operation zu einer Invalidierung der Indizes, sofern nicht die Klausel UPDATE INDEXES verwendet wird.
• um Verschiebungen von Datensätzen über Partitionsgrenzen zu erlauben, muss row movement aktiviert sein: dabei muss es erst auf child-, dann auf parent-Ebene aktiviert werden (bei umgekehrter Reihenfolge ergibt sich ein Fehler "ORA-14662: row movement cannot be enabled".

Viele weitere Fragen würden mir in diesem Zusammenhang auch nicht mehr einfallen (außer vielleicht, ob sich split partition entsprechend verhält, wovon ich erst einmal ausgehe). In jedem Fall ein herzlicher Dank an den Autor für die rasche Beantwortung dieser Fragen.

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.

Falsche Ergebnisse durch Partitions-Operationen "without validation"

Als Ergebnis einer Oracle-L-Diskussion hat Jonathan Lewis einen Artikel veröffentlicht, der einen Fall beschreibt, in dem der Einsatz eines Index-Hints ein falsches Ergebnis liefert, ohne dass dieses Verhalten als Oracle-Bug zu betrachten wäre. Während der Zugriff ohne den Index-Hint einen Full Table Scan mit Partition Pruning mit sich bringt, der die relevanten Daten nur aus der korrekten Partition liest, führt der Zugriff über den Index (ohne folgenden Tabellen-Zugriff) dazu, dass ein zusätzlicher Datensatz zurückgeliefert wird, der in der falschen Partition abgelegt wurde. Dass dies möglich war, ergab sich aus der Verwendung von Partition Exchange "without vaildation". Daher ist der Herr Lewis der Meinung, dass man sich schlecht über die inkonsistenten Ergebnisse beschweren kann, wenn man dem System vorher explizit die Anweisung gegeben hat, die Daten nicht zu überprüfen - und das scheint mir eine einleuchtende Position zu sein.

Table Expansion Bug mit Interval Partitioning

Als das Interval Partitioning in 11g eingeführt wurde, schien mir das eine der besten Ideen gewesen zu sein, die Oracle seit Einführung der Partitionierung eingefallen waren. Leider hat sich im Verlauf der Zeit herausgestellt, dass die Implementierung eine ziemliche große Zahl von Problemen hervorgerufen hat, von denen mir erstaunlich viele im Rahmen meiner eigenen Arbeit begegnet sind. Einen weiteren bizarren Bug, der in diesem Zusammenhang auftreten kann, hat Jonathan Lewis vor kurzem beschrieben: in 12c ergeben sich unter Umständen falsche Ergebnisse, wenn man Table Expansion - also die Möglichkeit, verschiedene Partitionen einer Tabelle mit unterschiedlichen Zugriffsstrategien abzufragen - in Verbindung mit Interval Partitioning verwendet. Im Beispiel wird - via Hint expand_table - ein Index-Zugriff auf eine Partition hervorgerufen, während die übrigen Partitionen per Full Table Scan gelesen werden. Der daraus resultierende Plan enthält zunächst die erwarteten Elemente: das UNION ALL, einen step PARTITION RANGE SINGLE für den Index-Zugriff und einen step PARTITION RANGE ITERATOR mit dem FULL TABLE SCAN für die Partitionen 2 bis 4. Seltsamerweise folgt dann aber noch ein step PARTITION RANGE INLIST ohne Partitionsangaben. Das könnte noch ein Darstellungsfehler im Plan sein, aber die rowsource Statistiken zeigen, dass tatsächlich die doppelte Anzahl von Datensätzen zurückgegeben wird. Zur Eingrenzung des Problems wurden noch folgende Prüfungen durchgeführt:

• in 11.2.0.4 wird die Table Expansion beim Zugriff auf eine intervallpartitionierte Tabelle auch beim Einsatz des Hints expand_table nicht verwendet.
• in 11g und 12c ergibt sich das Problem nicht, wenn keine Intervallpartitionierung im Spiel ist.

Klar ist an dieser Stelle zunächst nur, dass hier ein Bug im Spiel ist und dass 12c offenbar Table Expansion für intervallpartitionierte Tabellen in Fällen zulässt, in denen das in 11g noch nicht vorgesehen war. Doppelt schade, denn Table Expansion halte ich im Grunde für ein ähnlich interessantes Feature wie Interval Partitioning.

"Fixed Subqueries" und Partitionierte Tabellen

Jonathan Lewis weist in seinem aktuellen Scratchpad-Artikel darauf hin, dass neue Features des Optimizers nicht immer in allen relevanten Zusammenhängen folgerichtig integriert werden. Das Beispiel, an dem diese Schwierigkeit aufgezeigt wird, ist das der "fixed subqueries" - also Queries der Form "select 42 from dual" -, bei denen der Optimizer (seit 12c) dazu in der Lage ist, zu erkennen, dass der Wert 42 invariant ist, und daher bereits bei der Optimierung berücksichtigt werden kann. Im Artikel wird gezeigt, dass der Optimizer erwartungsgemäß dazu in der Lage ist, einschränkende Prädikate solcher "fixed subqueries" bei den Cardinality-Schätzungen korrekt zu berücksichtigen - und dass das nicht funktioniert, wenn der statische Wert durch eine Funktion verschleiert wird (also wenn satt 42 eine Funktion f(42) erscheint, die 42 zurückliefert). Wenn man aber statt einer einfachen eine partitionierte Tabelle verwendet, ergibt sich die erwähnte Uneinheitlichkeit: für den Fall der Funktionsverwendung ist das Verhalten folgerichtig, aber beim Einsatz des unveränderten Literalwertes wird dieser zwar bei der Cardinality-Schätzung berücksichtigt, nicht aber bei der Bestimmung der Pstart und Pstop values, die mit den Angaben KEY - KEY erscheinen - also zum Compile-Zeitpunkt anscheinend als unbekannt betrachtet werden. Offenbar ist das Verhalten also noch nicht in allen Zusammenhängen konsistent, was vermutlich in folgenden Releases korrigiert werden wird.

INDEX FULL SCAN (MIN/MAX) und partitionierte Tabellen

Eine Frage, die ich mir gelegentlich schon einmal gestellt hatte, und dachte, die Antwort hier im Blog bereits notiert zu haben, lautet: ist der INDEX FULL SCAN (MIN/MAX) als Zugriffsoption auch für partitionierte Tabellen möglich? Da diese Antwort aber auf Anhieb unauffindbar zu sein scheint und womöglich von mir nie protokolliert worden ist, schreibe ich sie (noch einmal?) auf:

drop table t;
create table t
( id number
, startdate date
)
   partition by range (startdate)
   interval (numtoyminterval(1,'MONTH'))
  (partition p1 values less than ( to_date('01.07.2015','dd.mm.yyyy'))
  );

-- Daten einfügen
insert into t
select rownum
     , trunc(sysdate) + mod(rownum, 365)
  from dual 
connect by level <= 100000;

create index t_idx on t(startdate) local;

explain plan for
select min(startdate) from t;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     9 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL MIN/MAX|       |     1 |     9 |            |          |     1 |1048575|
|   2 |   SORT AGGREGATE            |       |     1 |     9 |            |          |       |       |
|   3 |    INDEX FULL SCAN (MIN/MAX)| T_IDX |     1 |     9 |     2   (0)| 00:00:01 |     1 |1048575|
-----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

select /*+ gather_plan_statistics */ max(startdate) from t;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |      1 |00:00:00.01 |       1 |      1 |
|   1 |  PARTITION RANGE ALL MIN/MAX|       |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
|   2 |   SORT AGGREGATE            |       |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| T_IDX |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Ich will nicht behaupten, dass da eine große Überraschung im Spiel ist: jeder einzelne lokale Index ist ein Segment und für jedes dieser Segmente ist ein INDEX FULL SCAN (MIN/MAX) möglich. Während das einfache Explain Plan die für interval partitions übliche - eher weniger hilfreiche - Bereichsangabe 1-1048575 für Pstart und Pstop liefert, zeigt der Plan mit rowsource statistics recht deutlich, dass tatsächlich nur auf ein Segment zugegriffen wird, denn Buffers = 1 wäre für einen Zugriff auf mehrere lokale Indizes schwer zu erklären. Auch das ist zunächst keine Überraschung, denn startdate ist schließlich der partition key, so dass hier partition pruning möglich sein sollte und anschließend dann der INDEX FULL SCAN (MIN/MAX) auf der (zeitlich) letzten Partition. Überraschender ist da schon die PARTITION RANGE ALL MIN/MAX Angabe, denn eigentlich ist hier kein Scan aller Partitionen erforderlich und findet laut rowsource statistics auch nicht statt. Um die Annahme, dass hier tatsächlich partition pruning im Spiel (und der Execution Plan nicht so ganz plausibel) ist, zu überprüfen, habe ich - den Erläuterungen von Christoph Bohl folgend - noch ein Trace mit Event 10128 erzeugt und darin Folgendes gefunden:

Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = DESCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 4]
   index = 4
  current partition: part# = 4, subp# = 1048576, abs# = 4

An dieser Stelle bin ich mir der Semantik nicht so ganz sicher: einerseits scheint die Iterator-Angabe einen Zugriff auf alle fünf Partitionen anzugeben, andererseits verweist current partition nur auf die (zeitlich) letzte Partition. Da mir die zweite Aussage besser in den Kram passt, halte ich sie zunächst mal für die korrekte Interpretation (zumal sie auch den Aussagen in der manuell erzeugten Hilfstabelle kkpap_pruning entspricht) - versuche aber noch, weitere Meinungen einzuholen.

P.S.: die geringe Anzahl der Partitionen ergibt sich übrigens daraus, dass meine Datenbank ein wenig in der Vergangenheit lebt und ihr sysdate im Oktober 2014 sieht.

Größe des Initial Extents für Partitionierte Tabellen

Dom Brooks weist in seinem Blog auf zwei Punkte hin, von denen mir (wie ihm) der erste komplett entgangen war:

• die Default-Größe des Initial Extents für partitionierte Tabelle ist seit 11.2.0.2 auf 8MB erhöht worden, davor betrug sie 64KB. Das kann im Fall geringfügig gefüllter Partitionen zur Verschwendung von Speicherplatz führen, wobei solche Partitionen natürlich unter Umständen auch ein Anlass sein könnten, über die Partitionierungsstrategie nachzudenken.
• der zweite Punkt war mir klar: da die Maximalanzahl der Partitionen in einer Tabelle 1024K -1 = 1048575 beträgt, kann man bei Interval Partitionierung bei Auswahl kleinerer Intervalle relativ leicht an die Begrenzungen stossen.

Beide Effekte werden anhand aussagekräftiger Beispiele erläutert.

Partion Views in 11.2

Zu den ersten Ergebnissen, die Google bei der Suche nach "Partition View" liefert, gehört Oracle7 Tuning, release 7.3.3. Jenes Release 7.3 wurde 1996 veröffentlicht und seit mehr als zehn Jahren wird man bei Oracle nicht müde zu betonen, dass die "Partition Views" als Feature deprecated sind - aber offenbar funktionieren sie auch in Release 11.2 noch immer, wie ich heute beim Durchspielen eines im OTN-Forum vorgestellten Beispiels feststellen konnte. Ich spare mir hier die Wiederholung des Versuchsaufbaus und der Analyse, die man im Thread nachlesen kann. An dieser Stelle nur eine kurze grundsätzliche Erläuterung: Partition Views waren eine Art Vorläufer der "echten" Partitionierung und bestehen aus einer View mit über UNION ALL verknüpften Basistabellen, bei denen Check-Constraints die Zuordnung von Werten zu einer bestimmten Basistabelle sicherstellen. Natürlich ist "echte" Partitionierung ein deutlich mächtigeres Werkzeug, aber ich neige zur Einschätzung von David Aldrige: "Considering the enormous cost of an upgrade to Enterprise Edition and the Partitioning Option, I'd consider them [i.e. Partition Views] if I was using Standard Edition. The extra work is manageable."

Nachtrag 09.08.2014: eine dazu passende Diskussion unter Teilnahme von Tim Gorman, Iggy Fernandez und Jonathan Lewis hat sich gerade auch in der oracle-l Liste ergeben.

Online -Reorganisation für Partitionierte Tabellen mit 12c

Richard Foote schreibt in seinem Blog über die Möglichkeiten der Online Reorganisation von Tabellen in den Releases 11 und 12:

• 12c Online Partitioned Table Reorganisation Part I (Prelude) erklärt das Verhalten in Version 11:
• ALTER TABLE ... MOVE ONLINE ist keine gültige Option, da die ONLINE-Option nur für IOTs verfügbar ist.
• ein ALTER TABLE ... MOVE kann nicht erfolgen, wenn andere Sessions offene Transaktionen mit DML auf die Tabelle durchführen ("ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired").
• ein laufendes ALTER TABLE ... MOVE hindert umgekehrt andere Sessions an den Durchführung von DML-Operationen und hinterlässt alle zugehörigen Indizes im Status UNUSABLE.
• ein ähnliches Verhalten ergibt sich bei der Reorganisation von Tabellen-Partitionen.
• die Alternative dbms_redefinition hat ihre eigenen Probleme und ist deutlich unhandlicher.
• 12c Online Partitioned Table Reorganisation Part II (Move On) erläutert die Änderungen, die sich mit 12c ergeben haben:
•  Tabellen-Partitionen können jetzt online reorganisiert werden, wobei die zugehörigen Indizes aktualisiert werden und verfügbar bleiben.
• erforderlich ist dafür die Syntax: ALTER TABLE ... PARTITION ... UPDATE INDEXES ONLINE. 
• Die MOVE-Operation beim Vorliegen offener Transaktionen scheitert nun nicht mehr an ORA-00054, sondern wartet auf das Commit der zugreifenden Sessions, um ein exklusives table partition lock bekommen zu können.
• die zugehörigen Indizes bleiben im Status USABLE.
• nach dem Start des MOVE-Kommandos abgesetzte DML-Operationen anderer Sessions können problemlos durchgeführt werden.
• Leider betrifft die Möglichkeit der Online Reorganisation zur Zeit nur partitionierte Tabellen, während nicht-partitionierte Tabellen davon ausgeschlossen sind - was ein Argument dafür sein könnte, solche Tabellen als partitionierte Objekte mit einer Partition anzulegen.

Vereinfachte Administration für Partitionierte Tabellen in 12c

Gwen Lazenby zeigt im Blog der Oracle University ein paar nette Verbesserungen bei der Administration partitionierter Tabellen, die mit Release 12c eingeführt wurden. Hauptsächlich geht es dabei um die Möglichkeit, diverse Partitionen mit einem einzelnen Kommando zu behandeln - also zu erzeugen, zu splitten, zu verschmelzen oder zu löschen. Dabei enthält der Artikel recht umfangreiche Beispiele. Ich vermute, dass da relativ wenig Magie im Spiel ist - sprich: keine neue interne Logik -, sondern nur eine syntaktische Vereinfachung integriert wurde (habe mir die Details aber nicht angeschaut); aber gerade solche Vereinfachungen ersparen ermüdende Routinearbeit und reduzieren damit auch die Fehleranfälligkeit administrativer Operationen.

Falsche Ergebnisse durch Partition Exchange ohne Validation

Jonathan Lewis schreibt dieser Tage so viel, dass ich Mühe habe, mit der Lektüre zu folgen - vom Aufschreiben der zentralen Punkte ganz zu schweigen. Aber das heutige Quiz zeigt einen ziemlich gemeinen Effekt, der mir nur noch ganz vage in Erinnerung war und dort lieber einen besseren Platz bekommen sollte, nämlich die Tatsache, dass sich Oracle beim Partition Exchange without validation darauf verlässt, dass der Auftraggeber schon wissen wird, warum er etwas tut - und deshalb auch abwegige Ergebnisse liefern kann. Im Beispiel lieferte ein SELECT DISTINCT den gleichen Wert doppelt, da er einmal aus der korrekten Partition und einmal aus einer anderen Partition gelesen wurde, die eigentlich für ganz andere Werte vorgesehen war, aber ohne Prüfung durch den Partitionsaustausch an die unpassende Stelle gelangte. Wahrscheinlich irritieren mich solche Effekte in relationalen Datenbanken deutlich mehr als anderswo, weil ich damit rechne, dass das RDBMS mich schon davon abhalten wird, ungeheuren Blödsinn anzustellen.

MV Refresh über Partition Exchange

Jonathan Lewis beschreibt in seinem Blog ein Verfahren, das ich in ähnlicher Weise gelegentlich auch schon verwendet hatte - und mich dabei immer gewundert habe, dass es nicht häufiger eingesetzt bzw. in technischen Blogs beschrieben wird: die Verwendung von Partition Exchange zum performanten Austausch einer Materialized View gegen eine neu erzeugte prebuilt table. Die Idee dabei ist einfach, dass eine MV, die permanent für Abfragen verfügbar bleiben muss, nicht einfach über truncate und insert append neu befüllt werden kann (wie es die atomic_refresh Option erlaubt). Stattdessen kann man aber den Neuaufbau der MV in einer Hilfstabelle durchführen, die man dann gegen die bisher verwendete Tabelle per Partition Exchange austauscht, was natürlich voraussetzt, dass die MV als partitionierte Tabelle mit einer einzigen Partition angelegt wurde. Da in der MV nach dem Aufbau keine weiteren DML-Operationen durchgeführt werden, kann man die Segmente der Tabelle und zugehöriger Indizes so klein wie möglich machen und zur Beschleunigung des Aufbaus kann man diesen auch noch als nologging durchführen, um die Generierung von undo und redo zu verringern. Der Artikel beruhigt mich, denn ich hatte immer das vage (und unangenehme) Gefühl, bei meiner Implementierung - die allerdings keine echten MVs, sondern reguläre Dimensionstabellen erzeugte - irgendetwas Wichtiges übersehen zu haben, was aber anscheinend nicht der Fall ist.

Fehlende Partitions-Statistiken

Doug Burns schrieb es dieser Tage in seinem Blog: "blogging is over!" Möglicherweise stimmt das sogar, aber solange ich keine Beispiele dafür finde, dass jemand eine umfassende technische Erläuterung auf 140 Zeichen unterbringt, bleibe ich Blog-Schreiber und -Leser. Möglicherweise neige ich inzwischen auch zu Sentimentalität - oder einfach zur Senilität; die Grenze dazwischen ist vermutlich nicht immer ganz scharf.

Immerhin hat der Herr Burns seine Bemerkung mit dem Vorsatz verbunden, wieder häufiger zu bloggen und das fände ich gerade in seinem Fall auch sehr erfreulich. Sein erster technischer Beitrag nach einer längeren Pause trägt den Titel 10053 Trace Files - Global Stats on Partitioned Tables und beschäftigt sich mit den Analyse-Möglichkeiten von Optimizer Trace Files zur Beantwortung von Fragen nach der Verwendung lokaler oder globaler Statistiken. Dabei ist klar, dass der CBO globale Statistiken verwendet, sobald auf mehr als eine Partition zugegriffen wird, und dass lokale Partitions-Statistiken verwendet werden, wenn der Zugriff sich auf die fragliche Partition beschränkt. Die Frage im Artikel lautet: was passiert, wenn beim Zugriff auf die einzelne Partition keine Statistiken für diese Partition vorliegen? In diesem Fall weicht der CBO auf die globalen Statistken aus (und nicht etwa auf dynamic sampling), was im CBO-Trace durch den Hinweis "(Using composite stats)" ausgewiesen ist. Eine große Überraschung ist das eher nicht, aber es ist schön, einen Test als expliziten Beleg dafür zu haben.

Asynchrone Aktualisierung globaler Indizes in 12c

Richard Foote hat sich in einer Artikelserie mit dem Thema Global Index Maintenance auseinandergesetzt. Dabei behandeln die einzelnen Artikel folgende Aspekte:

• Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed): erläutert die Situation in 11g: dort war die Löschung einer Tabellen-Partition eine sehr billige Operation, die allerdings alle zugehörigen globalen Indizes invalidierte (Status = 'UNUSABLE'). Alternativ konnte man dem drop partition Kommando auch die Klausel "update global indexes" zufügen, die dann aber erwartungsgemäß hohe Kosten (db block gets und redo) hervorrief.
• 12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?): in 12c ist die Verwendung der Klausel "update global indexes" sehr viel günstiger geworden: sie ruft keine unmittelbare Reorganisation hervor, sondern führt nur zu ORPHANED_ENTRIES, also Index-rowid-Verweisen ins Nirgendwo. Die erforderlichen Maintenance-Operationen erfolgen asynchron. Der Verzicht auf die unmittelbare Reorganisation bedeutet dabei allerdings, dass die gelöschten Index-Einträge von folgenden DML-Operationen nicht direkt wiederverwendet werden können, was zu einem Wachstum des Index führen kann (jedenfalls, wenn der Index nonunique ist, was in Teil 3 genauer erläutert wird).
• 12c Asynchronous Global Index Maintenance Part II (The Space Between): untersucht das interne Vorgehen anhand eines Vergleichs von Index Block Dumps aus 11g und 12c: in 11g werden die Verweise auf die gelöschte Partition mit dem Flag D (= deleted) versehen, während im 12er Dump des Index keine Hinweise auf die Löschung zu finden sind. Das fehlen der Information in 12c macht die Wiederverwendung von Einträgen verständlicherweise unmöglich. Um eine Reorganisation der Index-Struktur hervorzurufen, gibt es verschiedene Möglichkeiten:
• ALTER INDEX ... REBUILD PARTITION ...; ruft einen vollständigen Neuaufbau der Index-Struktur hervor, was effektiv, aber auch teuer ist.
• ALTER INDEX ... COALESCE CLEANUP; löscht die orphaned entries aus der Index-Struktur, was billiger ist als die Rebuild-Operation.
• PMO_DEFERRED_GIDX_MAINT_JOB - führt die Reorganisation automatisch im vorgesehenen Maintenance-Window durch.
• 12c Asynchronous Global Index Maintenance Part III (Re-Make/Re-Model): zeigt, dass im Fall eines unique index eine Wiederverwendung der orphaned entries unmittelbar möglich ist, wenn der gleiche Wert erneut indiziert wird (was der Herr Foote gelegentlich schon mal erläutert hatte).

Möglicherweise wird die Serie noch weiter fortgesetzt, worauf ich dann vermutlich reagieren würde.

Partielle Indizes für partitionierte Tabellen in Oracle 12c

Richard Foote schreibt eifrig über neue Features in 12c und ich habe Mühe, bei der Lektüre schrittzuhalten. Zum Thema der Möglichkeit, Indizes nur für eine Teilmenge der Partitionen einer Tabelle anzulegen, hat er folgende Artikel veröffentlicht:

• 12c Partial Indexes For Partitioned Tables Part I (Ignoreland): weist zunächst darauf hin, dass partielle Indizes nicht auf lokale Indizes beschränkt sind - was ich besonders erstaunlich finde -, und zeigt das Verhalten zunächst anhand eines globalen Index. Das Schlüsselwort für die Festlegung, welche Partitionen indiziert werden sollen, lautet INDEXING (was ich etwas einfallslos finde), und ein partieller Index wird über die Syntax CREATE INDEX ... INDEXING PARTIAL; angelegt. Zugriffe, die sowohl indizierte als auch nicht indizierte Partitionen betreffen, werden im Execution Plan über UNION ALL verknüpft. Durch geschickte (Sub-) Partitionierung kann man auf diese Weise die Größe (und Anzahl) von Index-Partitionen auf ein Minimum reduzieren.
• 12c Partial Indexes For Partitioned Tables Part II (Vanishing Act): liefert ein Beispiel für das Verhalten mit lokalen Indizes. In diesem Fall werden die Index-Partitionen, für Partitionen, die mit INDEXING OFF definiert sind, als UNUSABLE erzeugt, was dafür sorgt, dass kein entsprechendes Segment angelegt wird. Aus nahe liegenden Gründen kann ein partieller Index nicht zur Unterstützung von PK- und UK-Constraints verwendet werden.

Dürfte sich als ein extrem nützliches Feature erweisen, nehme ich an.