Kosten für FTS mit IN-List

Im Kapitel 3 seines CBO-Buchs (S. 45 ff.) untersucht Jonathan Lewis die Cardinality-Schätzungen des CBO bei IN-Listen und entdeckt dabei allerlei kleinere Unterschiede zwischen den betrachteten Releases - vor allem hinsichtlich des Umgangs mit Werten, die außerhalb der bekannten Werte-Ranges einer Spalte liegen. Deshalb prüfe ich hier mal das Verhalten von 11.2.0.1 mit ähnlichen Beispielen:

-- Tabelle mit 120.000 Sätzen, jeweils 10.000 Sätze pro mon_col-Wert
create table test_inlist
as
select rownum pk_col
     , mod(rownum, 12) mon_col
     , lpad('*', 100, '*') pad_col
  from dual
connect by level <= 120000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST_INLIST', estimate_percent=>dbms_stats.auto_sample_size)

-- Statistiken für mon_col
select column_name
     , num_distinct
     , num_nulls
     , density
     , num_buckets
  from user_tab_columns
 where table_name = 'TEST_INLIST'
   and column_name = 'MON_COL';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY NUM_BUCKETS
------------------------------ ------------ ---------- ---------- -----------
MON_COL                                  12          0 ,083333333           1

Also die erwartete Density von 1/12 und offenbar keine Histogramme im Spiel (die in diesem Fall aber auch irrelevant wären, da die Daten ja völlig gleichverteilt sind). Jetzt die unterschiedlichen Testfälle mit folgender Testquery:

select count(*)
  from test_inlist
 where mon_col in (...)

Überprüft werden die Fälle, die schon in Jonathan Lewis betrachtet wurden:

• mon_col in (25): Wert oberhalb des high_value
• mon_col in (4, 4): wiederholte Werte
• mon_col in (3, 25): Werte innerhalb und außerhalb des bekannten Bereichs
• mon_col in (3, 25, 26): dito
• mon_col in (3, 25, 26, 27, 28): dito
• mon_col in (3, 25, 25, 25, 26, 26, 26): dito, mit Wiederholungen
• mon_col in (3, 25, null): werden NULL-Werte erkannt
• den Test mit Bindewerten lasse ich erst mal aus

Hier nur die Ergebnisse:

Prädikat                               |        rows      |   Kommentar
---------------------------------------|------------------|-------------------------------------
mon_col in (25)                        |           1      |   ok
mon_col in (13)                        |        8182      |   jenseits des bekannten Bereichs
                                       |                  |   sinkt die Schätzung nicht sofort
                                       |                  |   auf 1, sondern langsam ab, wobei
                                       |                  |   ein Zusammenhang mit der Anzahl
                                       |                  |   distinkter Spalten-Werte besteht;
                                       |                  |   dieses Verhalten beschreibt Lewis
                                       |                  |   auf S. 49 bereits für 10.1.0.2
mon_col in (4, 4)                      |       10000      |   ok
mon_col in (3, 25)                     |       10000      |   ok
mon_col in (3, 25, 26)                 |       10001      |   wahrscheinlich liegt schon das
                                       |                  |   Ergebnis für 25 zwischen 0 und 1;
                                       |                  |   in der Summe kommt man für 25 und
                                       |                  |   26 dann über 1
mon_col in (3, 25, 26, 27, 28)         |       10002      |   bestätigt die Vermutung
mon_col in (3, 25, 25, 25, 26, 26, 26) |       10001      |   Wiederholungen scheinen immer
                                       |                  |   erkannt zu werden
mon_col in (3, 25, null)               |       20000      |   NULL-Werte werden offenbar immer
                                       |                  |   noch nicht korrekt bewertet

Offenbar hat der cbo set 10.1 allerlei gelernt, aber mit NULL-Werten hat er anscheinend immer noch Probleme.