Cardinality Schätzungen für TEMP TABLE TRANSFORMATION

Randolf Geist hat vor einigen Wochen in zwei Artikeln weitere Details zum Verhalten der Cardinality-Schätzungen im Rahmen der TEMP TABLE TRANSFORMATION geliefert - in Ergänzung zu zwei (geringfügig) älteren Artikeln, die ich hier gelegentlich verlinkt habe und in denen vor allem darauf hingewiesen wurde, dass die Transformation nicht kostenbasiert ist, sondern automatisch angewendet wird (sofern die erforderlichen Voraussetzungen erfüllt sind), dass sie zu einer Materialisierung aller Spalten der CTE führt - und nicht nur der tatsächlich relevanten (und somit unnütze Arbeit hervorrufen kann) -, und eben dass sie unerfreuliche Auswirkungen auf die Bestimmung der Cardinalities hat. Der Detaillierung dieses letzten Punktes dienen die beiden folgenden Artikel:

• Temp Table Transformation Cardinality Estimates - 1: liefert ein relativ einfaches Self-Join-Beispiel, bei dem die Query mit einem Inline-Hint als Hash Join mit zweifacher Verwendung der Basis-Tabelle ausgeführt wird und ohne den Hint als Hash Join der Materialisierung dieser Basistabelle. Der Join erfolgt dabei über einen Ausdruck (id + 1), was die Aufgabe des Optimizers im Fall der Materialisierung offenbar komplizierter macht: während die Inline-Variante die Cardinality 10000 liefert, ergibt sich für den Fall mit Temp Table Transformation eine Cardinality von 1. Tatsächlich ist die 1 laut CBO-Trace eine aufgerundete 0, denn offenbar verliert der Optimizer durch die Transformation diverse statistische Informationen, insbesondere zu den Spalten-Statistiken. Besonders unerfreulich ist dabei, dass sich die Fehler in der Schätzung - zumindest vor 12c - nicht mit Hilfe des opt_estimate Hints korrigieren lassen. In 12c wird der Hint dann nicht mehr übergangen, führt aber zu anderen Merkwürdigkeiten. Für das vorliegende Beispiel lässt sich das Problem durch extended statistics für den Ausdruck id + 1 beheben, aber es bleibt festzuhalten das die Temp Table Transformation massive Auswirkungen auf die Cardinality-Schätzungen haben kann, und dass die Korrektur der dadurch entstehenden Fehler vor 12c nicht mit Hilfe der klassischen Hints durchgeführt werden kann.
• Temp Table Transformation Cardinality Estimates - 2: erweitert das Beispiel des ersten Artikels um eine zusätzliche Filterung der id-Spalte, der auf beiden Seiten des Joins 99% der Daten zum Opfer fallen. Während die inline-Version des Ausführungsplan dieser Veränderung der Datenmengen Rechnung trägt, wird die Filterung im Fall der Temp Table Transformation komplett ignoriert. Insofern kann die Transformation in vielen Fällen zu einer deutlichen Überschätzung der Cardinalities führen. Da die Filterung im Fall der Transformation nicht beim step des Tabellenzugriffs erfolgt, sondern erst im folgenden view-step, ergeben sich im Exadata-Fall Zugriffe, die eigentlich vermeidbar wären.

Ich werde wohl in Zukunft häufiger als bisher auf den Inline-Hint zugreifen, um zu bestimmen, wann die Temp Table Transformation den Optimizer in die Irre führt.

Nachtrag 02.08.2015: Jonathan Lewis hat zuletzt zwei Artikel veröffentlicht, in denen er auch noch einmal darauf hinweist, dass die TEMP TABLE TRANSFORMATION nicht kostenbasiert, sondern auf Basis einer Heuristik erfolgt. Er kommt darin auch (wieder einmal) zu dem Schluss, dass CTEs, die aus Gründen der Lesbarkeit eingeführt werden, manchmal die Query-Performance beeinträchtigen.