Histogramme - 3

Nachdem ich zuletzt ein ganz harmloses Testbeispiel zur Darstellung der Struktur von Frequency Histogrammen definiert hatte, um dann festzustellen, dass das Beispiel ganz so harmlos nicht war - und sich ganz anders verhielt, als ich vorher vermutet hatte - suche ich weiter nach einem Beleg dafür, dass sich Histogramme in weniger extremen Fällen tatsächlich so verhalten, wie ich behautptet hatte:

Zunächst lege ich eine Tabelle mit einem sehr häufigen Wert (100 mit 990.000 Sätzen) und 100 relativ seltenen Werten an, die jeweils 100 mal vorkommen.

 drop table test; 
create table test
as
select case when rownum <= 10000 then mod(rownum, 100) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 101')

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN'

COLUMN_NAME                    NUM_BUCKETS NUM_DISTINCT HISTOGRAM
------------------------------ ----------- ------------ ----------
RN                                      39          101 FREQUENCY

Nun ja, näher an den Erwartungen, aber noch nicht ganz dran ... - wieso sind es nur 39 buckets, statt der 101, die ich verlangt hatte.

Vielleicht ein Effekt der Sortierung der Daten? Immerhin dürften die Werte <> 100 ja auf die ersten 10.000 Sätze beschränkt sein. Deshalb werfe ich meine Testmenge mit dbms_random etwas durcheinander:

drop table test;
create table test
as
select case when rownum <= 10000 then mod(rownum, 100) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000
 order by dbms_random.value;
exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 101')

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                    NUM_BUCKETS NUM_DISTINCT HISTOGRAM
------------------------------ ----------- ------------ ---------
RN                                      42          101 FREQUENCY

Näher dran, aber noch immer nicht das, was ich erwartet hatte. Vielleicht ist der Wert 100 immer noch zu predominant - immerhin betrifft er immer noch 99% der Werte. Gehen wir auf 90% herunter.

create table test
as
select case when rownum <= 100000 then mod(rownum, 100) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 101')

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                    NUM_BUCKETS NUM_DISTINCT HISTOGRAM
------------------------------ ----------- ------------ ---------
RN                                     100          101 FREQUENCY

select column_name
     , ENDPOINT_VALUE
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN'
 order by column_name;

COLUMN_NAME                    ENDPOINT_VALUE
------------------------------ --------------
RN                                          0
RN                                          1
RN                                          2
RN                                          3
RN                                          4
RN                                          5
RN                                          6
RN                                          7
RN                                          9
RN                                         10
RN                                         11
...
RN                                         98
RN                                         99
RN                                        100

100 Zeilen ausgewählt.

Das wäre dann also - endlich - das erwartete Resultat. Anscheinend ist der von Tom Kyte angesprochene predominant value bei 90% der Gesamtsätze nicht mehr so übermächtig, dass er die bucket-Anzahl reduzieren würde. Für heute reicht mir das.