Probleme mit table functions und cardinality Hints

Matthias Rogel weist auf ein Problem hin, das sich bei der Kombination der nützlichen (allerdings auch undokumentierten) Hints cardinality (bzw. der entsprechenden aktuellen Variante opt_estimate - mit denen man dem Optimizer Informationen zur tatsächlichen Größe eines Zwischenergebnisses geben kann) und materialize (zur Materialisierung einer CTE) mit table functions ergibt (für die die Größenabschätzungen bekanntlich immer problematisch sind - Details zum Thema und zu den möglichen Lösungen findet man bei Adrian Billington): in einem solchen Fall wird der cardinality Hint nur berücksichtigt, wenn er zusammen mit dem materialize Hint in der CTE-Definition (also WITH-clause) erscheint, was die Möglichkeiten der Einflussnahme über den Hint deutlich beschränkt.

Das waren jetzt ziemlich viele Klammern und wahrscheinlich ist der vorangehende Abschnitt dadurch nicht lesbarer geworden, zumal es ihm an Details und Beispielen mangelt - ohne Lektüre des Basisartikels kann man damit vermutlich nicht viel anfangen. Dafür ergänze ich hier noch den praktischen Hinweis, dass im CBO-Trace (aka Event 10053) für 12c die Angaben zu den in der Query verwendeten Hints explizit aufgeführt werden und zwar am Ende des Trace-Files. Damit kann man endlich sehen, ob der Optimizer einen Hint aus inhaltlichen oder syntaktischen Gründen ignoriert hat:

-- 12.1.0.1
alter session set events '10053 trace name context forever, level 1';

SQL> select /*+ cardinality (t 10) */ count(*) from t;

  COUNT(*)
----------
     91600

Abgelaufen: 00:00:00.07
SQL> select /*+ cardianlity (t 10) */ count(*) from t;

  COUNT(*)
----------
     91600

alter session set events '10053 trace name context off';

Im erzeugten Trace-File ergeben sich folgende Angaben:

...
Dumping Hints
=============
  atom_hint=(@=0000000018424FD8 err=0 resol=1 used=1 token=1018 
  org=1 lvl=3 txt=OPT_ESTIMATE (TABLE "T"@"SEL$1" ROWS=10.000000 ) )
====================== END SQL Statement Dump ======================
...
Dumping Hints
=============
====================== END SQL Statement Dump ======================

Der korrekte Hint der ersten Query wird demnach angezeigt, während der falsch geschriebene Hint im zweiten Fall ignoriert wird.

Nachtrag 21.01.2014: Randolf Geist zeigt in einem Kommentar zum Artikel, dass es tatsächlich doch noch ein paar Möglichkeiten gibt, mit denen man einen cardinality Hint außerhalb der CTE platzieren kann.