NULL IS NOT NULL-Filter und Bindevariablen

Der folgende Test verdankt sich wieder einer Fragestellung im OTN-Forum, wobei ich die eigentliche Anfrage möglicherweise etwas frei interpretiert habe. Gegeben ist eine Query, in der in einer inline-View mehrere Subqueries über UNION ALL verknüpft sind, und in der äußeren Abfrage eine Einschränkung auf einen konstanten Wert erfolgt, der einige der Subqueries bedeutungslos macht, da er ihre Ergebnisse filtert. Aber wahrscheinlich erklärt ein Beispiel die Fragestellung sehr viel deutlicher:

drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum col1
  from dual
connect by level <= 1000;
 
create table t2
as
select rownum col1
  from dual
connect by level <= 1000;
 
create table t3
as
select rownum col1
  from dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 'T1')
exec dbms_stats.gather_table_stats(user, 'T2')
exec dbms_stats.gather_table_stats(user, 'T3') 

explain plan for
select coll, col1
  from (select 'AAA' coll, col1 from t1
          union all
        select 'BBB' coll, col1 from t2
          union all
        select 'CCC' coll, col1 from t3
       )
 where coll = 'AAA';

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1002 | 18036 |     4   (0)| 00:00:01 |
|   1 |  VIEW                |      |  1002 | 18036 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL | T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(NULL IS NOT NULL)
   6 - filter(NULL IS NOT NULL)

Der Plan zeigt deutlich, dass nur aus T1 Werte ausgelesen werden, während für T2 und T3 ein Filter-Prädikat "filter(NULL IS NOT NULL)" erscheint, das immer false liefern muss. Dazu passt (zumindest ungefähr) auch die Prognose der Größe der Ergebnismenge (rows = 1002). Aber was passiert, wenn statt des Literals ein Bindewert als Einschränkung verwendet wird?

var test varchar2(30);
exec :test := 'AAA';

explain plan for
select coll, col1
  from (select 'AAA' coll, col1 from t1
          union all
        select 'BBB' coll, col1 from t2
          union all
        select 'CCC' coll, col1 from t3
       )
 where coll = :test

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  3000 | 54000 |    12   (0)| 00:00:01 |
|   1 |  VIEW                |      |  3000 | 54000 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  5 |    FILTER            |      |       |       |            |          |
|   6 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  7 |    FILTER            |      |       |       |            |          |
|   8 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter('AAA'=:TEST)
   5 - filter('BBB'=:TEST)
   7 - filter('CCC'=:TEST)

Anstelle der "filter(NULL IS NOT NULL)" erscheinen hier ganz konventionelle Prädikate, die den Eindruck erwecken, als wäre hier eine frühzeitige Filterung ausgeschlossen - also ein Verzicht auf den Tabellenzugriff nicht möglich (was sich auch in der rows-Angabe 3000 widerspiegelt). Das ist insofern einleuchtend, als der Optimizer zum Zeitpunkt der Optimierung die Bindewerte nicht kennt und folglich auch keine Vorstellung von ihren Filtereigenschaften haben kann. Ein Blick auf die (hier nicht aufgeführten autotrace-) Statistiken der Ausführung macht aber deutlich, dass die Variante mit dem Bindewert die gleiche Anzahl an LIOs hervorruft wie die Variante mit Literal. Offenbar ist der Plan der Bindewert-Query ein wenig irreführend.

Dazu hat Randolf Geist im Thread die Erklärung geliefert:

the FILTER operation is always checked first if it can be fully evaluated without having to refer to the child operations of the FILTER - and in case of the expression :TEST = this is possible - so the underlying operations won't be started if the expression can be evaluated to FALSE. This could be confirmed by running the same test with rowsource statistics enabled (or via Real-Time SQL Monitoring) - the corresponding skipped child operations should be shown with STARTS = 0.

Auf die Idee der Verwendung von rowsource Statistiken hätte ich vielleicht auch kommen können - schließlich empfehle ich diese Technik im OTN-Forum regelmäßig selbst. In jedem Fall hat der Herr Geist recht, was die Vorhersage des Trace-Ergebnisses angeht:

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |   1000 |00:00:00.01 |      71 |
|   1 |  VIEW                |      |      1 |   3000 |   1000 |00:00:00.01 |      71 |
|   2 |   UNION-ALL          |      |      1 |        |   1000 |00:00:00.01 |      71 |
|*  3 |    FILTER            |      |      1 |        |   1000 |00:00:00.01 |      71 |
|   4 |     TABLE ACCESS FULL| T1   |      1 |   1000 |   1000 |00:00:00.01 |      71 |
|*  5 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |
|   6 |     TABLE ACCESS FULL| T2   |      0 |   1000 |      0 |00:00:00.01 |       0 |
|*  7 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |
|   8 |     TABLE ACCESS FULL| T3   |      0 |   1000 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter('AAA'=:TEST)
   5 - filter('BBB'=:TEST)
   7 - filter('CCC'=:TEST)

Die Filter-Prädikate werden also zur Laufzeit tatsächlich vor dem Zugriff auf das zugehörige Segment ausgewertet und verhindern den Zugriff - was eine effektive Strategie darstellt. Das einzige kleine Problem dabei ist, dass man den Prädikaten dieses Verhalten nicht ansieht.