| General |
| Histogram Types |
Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms.
The type of histogram is stored in the HISTOGRAM column of the *TAB_COL_STATISTICS views (USER and DBA). This column can have values of HEIGHT BALANCED, FREQUENCY, or NONE.
In a height-balanced histogram, the column values are divided into bands so that each band contains
approximately the same number of rows. The useful information that the histogram provides is where in
the range of values the endpoints fall.
|
| Data Dictionary Objects |
| DBA |
ALL |
USER |
| dba_histograms |
all_histograms |
user_histograms |
| dba_part_histograms |
all_part_histograms |
user_part_histograms |
| dba_sub_part_histograms |
all_sub_part_histograms |
user_sub_part_histograms |
| dba_tab_histograms |
all_tab_histograms |
user_tab_histograms |
INDEX_HISTOGRAMS
|
| |
| Note: Histograms can be generated by two
different methods ... both involve the keyword "SIZE". |
| Generation With DBMS_STATS |
dbms_stats.gather_table_stats(<schema_name>,
<table_name>,
METHOD_OPT => FOR COLUMN SIZE <integer> <column_name> |
desc user_histograms
set linesize 121
col column_name format a30
col endpoint_actual_value format a20
drop table servers purge;
drop table serv_inst purge;
@c:\temp\servers.sql
exec DBMS_STATS.GATHER_TABLE_STATS('UWCLASS', 'SERVERS', METHOD_OPT => 'FOR COLUMNS SIZE 3 srvr_id');
exec DBMS_STATS.GATHER_TABLE_STATS('UWCLASS', 'SERV_INST', METHOD_OPT => 'FOR COLUMNS SIZE 3 srvr_id');
SELECT *
FROM user_histograms
WHERE table_name = 'SERVERS';
SELECT *
FROM user_histograms
WHERE table_name = 'SERV_INST';
SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'SERVERS';
SELECT COUNT(*) FROM servers WHERE srvr_id < 522;
SELECT COUNT(*) FROM servers WHERE srvr_id BETWEEN 522 AND 568;
SELECT COUNT(*) FROM servers WHERE srvr_id > 568;
SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'SERV_INST';
exec DBMS_STATS.GATHER_TABLE_STATS('UWCLASS', 'SERV_INST',
METHOD_OPT => 'FOR COLUMNS SIZE 20 srvr_id');
SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'SERV_INST';
SELECT *
FROM user_histograms
WHERE table_name = 'SERV_INST'; |
| Legacy generation with ANALYZE |
ANALYZE TABLE <schema.object_name>
COMPUTE STATISTICS FOR COLUMNS <column_name>
SIZE <number_of_buckets_integer> |
ANALYZE TABLE servers
COMPUTE STATISTICS FOR COLUMNS srvr_id
SIZE 3; |
SELECT status, COUNT(*)
FROM all_obects
GROUP BY status;
CREATE TABLE demo AS SELECT * FROM all_objects;
set autotrace traceonly explain
SELECT COUNT(*)
FROM demo
WHERE status = 'INVALID';
exec DBMS_STATS.GATHER_TABLE_STATS('UWCLASS', 'DEMO',
METHOD_OPT => 'FOR COLUMNS SIZE 3 status');
SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'DEMO';
SELECT *
FROM user_histograms
WHERE table_name = 'DEMO';
|
|
| Demo |
|
Histogram Demo |
drop table servers purge;
drop table serv_inst purge;
@c:\temp\servers.sql
set autotrace traceonly explain
select s.srvr_id, i.type
from servers s, serv_inst i
where s.srvr_id = i.srvr_id
AND type = 'WIN';
select s.srvr_id, i.type
from servers s, serv_inst i
where s.srvr_id = i.srvr_id
AND type = 'MAC';
exec DBMS_STATS.GATHER_TABLE_STATS('UWCLASS', 'SERV_INST', METHOD_OPT => 'FOR COLUMNS SIZE SKEWONLY');
select s.srvr_id, i.type
from servers s, serv_inst i
where s.srvr_id = i.srvr_id
AND type = 'WIN';
select s.srvr_id, i.type
from servers s, serv_inst i
where s.srvr_id = i.srvr_id
AND type = 'MAC'; |
|