Cardinality für IN-Listen mit NULL-Werten in 12.1

Ein sperriger Titel für eine einfache Beobachtung. Vor etwas mehr drei Jahren hatte ich hier überprüft, wie sich die von Jonathan Lewis in Cost Based Oracle beschriebene Berechnung von Cardinalities für IN-Listen unterschiedlicher Struktur in neueren Releases verändert hatte. Zu den Fällen, die im Buch untersucht wurde, gehörten:

• wiederholte Werte
• Werte außerhalb des Korridors bekannter Werte (not between low_value and high_value)
• NULL-Werte

Wobei es zu jedem dieser Fälle unterschiedliche Varianten gab. Bereits in 11.2.0.1 verhielten sich alle untersuchten Fälle bis auf einen ganz plausibel. Der verbliebene Fall war die Behandlung von NULL-Werten - und diesen Fall habe ich mir jetzt noch mal mit 12.1.0.1 angesehen:

-- 12.1.0.1
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)

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

select count(*)
  from test_inlist
 where mon_col in (3, 25, null);

Plan hash value: 3620027018

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3 |   529   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_INLIST | 10000 | 30000 |   529   (1)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - filter("MON_COL"=3 OR "MON_COL"=25)

Die einschränkende Bedingung enthält somit einen Wert innerhalb des bekannten Wertebereichs (3), einen Wert weit jenseits des bekannten Wertebereichs (25) und einen NULL-Wert. In 11.2.0.1 wurde dafür noch eine Cardinality von 20000 berechnet: 10000 für den Wert 3, 0 für den Wert 25 und wieder 10000 für den Wert NULL. In 12.1 ist das Verhalten korrigiert, denn mon_col in (NULL) liefert natürlich kein Ergebnis, so dass die angenommenen 10000 dem tatsächlichen Ergebnis entsprechen.