Cardinality-Schätzung mit METHOD_OPT FOR ALL INDEXED COLUMNS

Eigentlich wollte ich heute etwas ganz anderes untersuchen, aber das, was ich zeigen wollte, konnte ich nicht reproduzieren - und deshalb zeige ich jetzt einen möglicherweise verwandten Fall, der sich in meinem Test ergeben hat. Gegeben ist eine nach Monatsdatumsangaben LIST-partitionierte Tabelle mit zehn Partitionen, in denen jeweils 1000 Sätze vorliegen (die elfte Partition für 1970 spielt hier gar keine Rolle, aber ich mag sie jetzt nicht mehr entfernen):

-- 11.1.0.7
drop table list_part;

create table list_part (
    start_date date
  , col1 number
)
partition by list (start_date) 
(
    partition P_min values (to_date('01.01.1970', 'dd.mm.yyyy'))
  , partition P201201 values (to_date('01.01.2012', 'dd.mm.yyyy'))
  , partition P201202 values (to_date('01.02.2012', 'dd.mm.yyyy'))
  , partition P201203 values (to_date('01.03.2012', 'dd.mm.yyyy'))
  , partition P201204 values (to_date('01.04.2012', 'dd.mm.yyyy'))
  , partition P201205 values (to_date('01.05.2012', 'dd.mm.yyyy'))
  , partition P201206 values (to_date('01.06.2012', 'dd.mm.yyyy'))
  , partition P201207 values (to_date('01.07.2012', 'dd.mm.yyyy'))
  , partition P201208 values (to_date('01.08.2012', 'dd.mm.yyyy'))
  , partition P201209 values (to_date('01.09.2012', 'dd.mm.yyyy'))
  , partition P201210 values (to_date('01.10.2012', 'dd.mm.yyyy'))
);

insert into list_part
select add_months(to_date('01.01.2012', 'dd.mm.yyyy'), trunc((rownum - 1)/1000) ) start_date
     , rownum col1
  from dual
connect by level <= 10000;  

commit;

-- Fall 1: Standard-Statistiken
exec dbms_stats.gather_table_stats(user, 'list_part')

explain plan for
select count(*) 
  from list_part
 where start_date >= to_date('20121001','yyyymmdd') 
   and start_date <= to_date('20121031','yyyymmdd');

select * from table(dbms_xplan.display);

-- Fall 2: keine Statistiken
exec dbms_stats.delete_table_stats(user, 'list_part')

explain plan for
select count(*) 
  from list_part
 where start_date >= to_date('20121001','yyyymmdd') 
   and start_date <= to_date('20121031','yyyymmdd');

select * from table(dbms_xplan.display);

-- Fall 3: method_opt => 'for all indexed columns'
exec dbms_stats.gather_table_stats(user, 'list_part', method_opt => 'for all indexed columns')

explain plan for
select count(*) 
  from list_part
 where start_date >= to_date('20121001','yyyymmdd') 
   and start_date <= to_date('20121031','yyyymmdd');

select * from table(dbms_xplan.display);

Die Ergebnisse des Tests sehen dann folgendermaßen aus:

-- Fall 1: Standard-Statistiken
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     8 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     8 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |  1000 |  8000 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |  1000 |  8000 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

-- Fall 2: keine Statistiken
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |  1000 |  9000 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |  1000 |  9000 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

-- Fall 3: method_opt => 'for all indexed columns' 
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |     3 |    27 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |     3 |    27 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

Fall 1 und Fall 2 liefern die korrekte Schätzung von 1000 rows aus der letzten Partition der Tabelle - einmal auf der Basis solider Statistiken und einmal auf der Basis von dynamic sampling. Nur Fall 3 liefert ein völlig anderes Ergebnis: da in der Tabelle kein Index existiert, erzeugt die method_opt => 'for all indexed columns' für keine Spalte globale Statistikinformationen (was diese recht verbreitete method_opt ziemlich gefährlich machen kann). Da in diesem Fall - ziemlich magere - Statistiken vorliegen, erfolgt kein dynamic sampling. Ohne Spalten-Statistik scheint der CBO aber auf die Arithmetik für bound ranges zurückzufallen, also Satzanzahl_der_Partition * 5% * 5% = 1000 * 0.05 * 0.05 = 2.5, was dann aufgerundet 3 ergibt. Wenn ich auf die Obergrenze des Ranges verzichte, erhalte ich:

explain plan for
select count(*)
  from list_part
 where start_date >= to_date('20121001','yyyymmdd');

select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |           |     1 |     9 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|           |    50 |   450 |     3   (0)| 00:00:01 |    11 |    11 |
|   3 |    TABLE ACCESS FULL   | LIST_PART |    50 |   450 |     3   (0)| 00:00:01 |    11 |    11 |
----------------------------------------------------------------------------------------------------

Das entspricht meiner Erwartung: 1000 * 0.5 = 50. Meine (vorläufige) Interpretation des Verhaltens lautet: durch Partition Elimination kann der CBO die relevante Partition bestimmen und rechnet in der Folge mit ihrer Satzanzahl. Das Fehlen von Spaltenstatistiken macht die Bestimmung der Filterung durch den Datumsrange dann aber zu einem Ratespiel, bei dem der CBO mit Standard-Selektivitäten rechnet. Theoretisch könnte der Optimizer erkennen, dass die gegebene Bedingung innerhalb der Partition keine Filterwirkung mehr haben kann, aber offenbar bringt er die Informationen zum Partition_Key und zum Datumsrange nicht zusammen. Da mir dergleichen auch mit schöner Regelmäßigkeit entgeht, will ich ihn dafür nicht kritisieren.

Im praktischen Fall, den ich nachbauen wollte, kam der CBO trotz vorliegender Spaltenstatistiken zu einem ähnlichen Ergebnis, aber da ich dieses Verhalten im Test nicht reproduzieren konnte, will ich dazu erst mal nichts Weiteres sagen.