create table test
as
select case when rownum <= 10000 then mod(rownum, 10) else 100 end rn
, lpad('*', 100, '*') pad
from dual
connect by level <= 1000000;
select rn
, count(*)
from test
group by rn
order by rn;
RN COUNT(*)
---- ----------
0 1000
1 1000
2 1000
3 1000
4 1000
5 1000
6 1000
7 1000
8 1000
9 1000
100 990000
Also 10 Werte mit jeweils 1.000 Vorkommen und einer, der 990.000 mal erscheint. Wenn man dazu USER_HISTOGRAMS befragt, sieht man Werte, die dazu in irgendeiner Relation zu stehen scheinen, aber die Art dieser Relation erschliesst sich nicht unmittelbar - zumindest nicht für mich:
select column_name , endpoint_value , endpoint_number from user_histograms where table_name = 'TEST' and column_name = 'RN'; COLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER ---------------------------------------- -------------- --------------- RN 0 65 RN 1 126 RN 2 171 RN 3 235 RN 4 292 RN 5 348 RN 6 400 RN 7 462 RN 8 532 RN 9 586 RN 100 5482
Die Antwort auf die Frage nach der Bedeutung dieser Werte kennt einmal mehr Jonathan Lewis: die ENDPOINT_NUMBER ist eine kumulierte Anzahl der Sätze pro Wert. Dankenswerterweise liefert er auf gleich eine Query mit deren Hilfe man die kumulierten Zahlen wieder in simple Gruppierungszählungen zurückverwandeln kann (über Analytics):
select endpoint_value column_value, endpoint_number - nvl(prev_endpoint,0) frequency from ( select endpoint_number, lag(endpoint_number,1) over( order by endpoint_number ) prev_endpoint, endpoint_value from user_tab_histograms where table_name = 'TEST' and column_name = 'RN' ) order by endpoint_number; COLUMN_VALUE FREQUENCY ------------ ---------- 0 65 1 61 2 45 3 64 4 57 5 56 6 52 7 62 8 70 9 54 100 4896
Vergleicht man diese Ergebnisse mit den Werten der gruppierenden Query oben, dann ergibt sich, dass die Frequency jeweils ca. 0,5% der absoluten Werte beträgt. Das wiederum lässt vermuten, dass hier ein Sampling im Spiel ist, und um das zu prüfen, habe ich ein kleines Script test.sql geschrieben, das die Statistiken mit unterschiedlicher estimate_percent-Angabe erhebt und anschließend die Query von Jonathan Lewis und eine Abfrage der SAMPLE_SIZE aus USER_TABELS durchführt:
-- test.sql
exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test', method_opt=>'FOR ALL COLUMNS SIZE 11', estimate_percent=> &sample_pct)
select
endpoint_value column_value,
endpoint_number - nvl(prev_endpoint,0) frequency
from (
select
endpoint_number,
lag(endpoint_number,1) over(
order by endpoint_number
) prev_endpoint,
endpoint_value
from
user_tab_histograms
where
table_name = 'TEST'
and column_name = 'RN'
)
order by
endpoint_number;
select table_name
, sample_size
from user_tab_columns
where table_name = 'TEST'
and column_name = 'RN';
Hier die Ergebnisse für verschiedene Sampling-Angaben:
SQL> @ test
Geben Sie einen Wert für sample_pct ein: 100
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:03.56
COLUMN_VALUE FREQUENCY
------------ ----------
0 10000
1 10000
2 10000
3 10000
4 10000
5 10000
6 10000
7 10000
8 10000
9 10000
100 900000
11 Zeilen ausgewählt.
Abgelaufen: 00:00:00.00
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
TEST 1000000
SQL> @ test
Geben Sie einen Wert für sample_pct ein: 10
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:00.90
COLUMN_VALUE FREQUENCY
------------ ----------
0 964
1 985
2 1054
3 984
4 995
5 956
6 978
7 986
8 1032
9 1003
100 89937
11 Zeilen ausgewählt.
Abgelaufen: 00:00:00.00
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
TEST 99874
SQL> @ test
Geben Sie einen Wert für sample_pct ein: 1
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:00.48
COLUMN_VALUE FREQUENCY
------------ ----------
0 88
1 112
2 98
3 91
4 91
5 104
6 94
7 120
8 102
9 106
100 9044
11 Zeilen ausgewählt.
Abgelaufen: 00:00:00.00
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
TEST 10050
SQL> @ test
Geben Sie einen Wert für sample_pct ein: dbms_stats.auto_sample_size
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:00.73
COLUMN_VALUE FREQUENCY
------------ ----------
0 58
1 61
2 43
3 55
4 48
5 55
6 38
7 47
8 66
9 51
100 4925
11 Zeilen ausgewählt.
Abgelaufen: 00:00:00.00
TABLE_NAME SAMPLE_SIZE
------------------------------ -----------
TEST 5447
Mit einem 100%-Sample kommt Jonathan Lewis' Analysequery also auf die gleichen Ergebnisse, die auch das GROUP BY Statement für das Auftreten der distinkten Werte liefert. Die anderen %-Samples liefern jeweils die erwarteten Prozentsätze. Mit der Angabe dbms_stats.auto_sample_size erhält man die Werte, die ohne Angabe von estimate_percent erscheinen - und das überrascht nicht, da diese Angabe laut Doku den internen Default darstellt.
Nachtrag 14.04.2012: die Sample Size von ca. 5.500 Sätzen ist der (nicht ganz unproblematische) Standard-Wert für die Histogrammerstellung mit auto_sample_size, wozu ich zuletzt hier ein paar Details aufgeführt habe.
Keine Kommentare:
Kommentar veröffentlichen