Search notes:
Oracle: DBA_TAB_HISTOGRAMS
dba_tab_histograms shows a table's
column's histogram values after gathering the respective statistics.
This view comes in all four variants: user_tab_histograms, all_tab_histograms, dba_tab_histograms and cdb_tab_histograms.
create table tq84_hist_test (
id number generated always as identity primary key,
vc varchar2(10),
nm number
);
insert into tq84_hist_test (vc, nm) values ('four' , 2);
insert into tq84_hist_test (vc, nm) values ('three', 3);
insert into tq84_hist_test (vc, nm) values ('four' , 4);
insert into tq84_hist_test (vc, nm) values ('four' , 1);
insert into tq84_hist_test (vc, nm) values ('two' , 4);
insert into tq84_hist_test (vc, nm) values ('two' , 3);
insert into tq84_hist_test (vc, nm) values ('three', 3);
insert into tq84_hist_test (vc, nm) values ('one' , 4);
insert into tq84_hist_test (vc, nm) values ('three', 2);
insert into tq84_hist_test (vc, nm) values ('four' , 4);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'tq84_hist_test',
method_opt => 'for columns' ||
' vc size 4' ||
' nm size 2'
);
end;
/
select
substr(endpoint_actual_value, 1, 10) end_value,
endpoint_number
from
user_tab_histograms
where
table_name = 'TQ84_HIST_TEST' and
column_name = 'VC'
order by
endpoint_number;
--
-- END_VALUE ENDPOINT_NUMBER
-- ---------- ---------------
-- four 4
-- one 5
-- three 8
-- two 10
select
substr(endpoint_actual_value, 1, 10) end_value,
endpoint_number
from
user_tab_histograms
where
table_name = 'TQ84_HIST_TEST' and
column_name = 'NM'
order by
endpoint_number;
--
-- END_VALUE ENDPOINT_NUMBER
-- ---------- ---------------
-- 1 1
-- 4 5
drop table tq84_hist_test;
Columns
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE The highest value in the bucket (in a «normalized» (and numerical) form). ENDPOINT_ACTUAL_VALUE ENDPOINT_ACTUAL_VALUE_RAW ENDPOINT_REPEAT_COUNT This value is only relevant for hybrid histograms. For all other histogram types, the value is 0. SCOPE
ENDPOINT_REPEAT_COUNT
This query demonstrates that the value of endpoint_repeat_count is only meaningful for hybrid histograms:
select
cst.histogram,
max(hst.endpoint_repeat_count) max_ep_rep_cnt
from
dba_tab_col_statistics cst join
dba_tab_histograms hst on cst.owner = hst.owner and
cst.table_name = hst.table_name and
cst.column_name = hst.column_name
group by
cst.histogram;