CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Histograms
Version 11.1
 
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.

In a frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. Frequency histograms can be viewed using the *TAB_HISTOGRAMS views.
Data Dictionary Objects
DBA ALL USER
dba_histograms all_histograms user_histograms
dba_part_histograms all_part_histograms user_part_histograms
dba_subpart_histograms all_sub_part_histograms user_sub_part_histograms
dba_tab_histograms all_tab_histograms user_tab_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: emp\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 table_name, column_name, endpoint_number, endpoint_value
FROM user_histograms
WHERE table_name = 'SERVERS';

SELECT table_name, column_name, endpoint_number, endpoint_value
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';
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;

Demo
SELECT status, COUNT(*)
FROM all_objects
GROUP BY status;

CREATE TABLE demo AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set autotrace traceonly explain

SELECT COUNT(*)
FROM demo
WHERE status = 'INVALID';

set autotrace off

exec dbms_stats.gather_table_stats('UWCLASS', 'DEMO', METHOD_OPT => 'FOR COLUMNS SIZE 3 status');

col table_name format a15
col column_name format a15

SELECT column_name, num_distinct, num_buckets, histogram
FROM user_tab_col_statistics
WHERE table_name = 'DEMO';

SELECT table_name, column_name, endpoint_number, endpoint_value
FROM user_histograms
WHERE table_name = 'DEMO';

Demo
conn sh/sh

SELECT COUNT(*)
FROM customers 
WHERE cust_state_province = 'CA';

SELECT COUNT(*) 
FROM customers 
WHERE cust_state_province = 'CA' 
AND country_id=52790;

SELECT COUNT(*) 
FROM customers 
WHERE cust_state_province = 'CA' 
AND country_id=52775;

set autotrace traceonly

SELECT COUNT(*)
FROM customers 
WHERE cust_state_province = 'CA';

SELECT COUNT(*) 
FROM customers 
WHERE cust_state_province = 'CA' 
AND country_id=52790;

SELECT COUNT(*) 
FROM customers 
WHERE cust_state_province = 'CA' 
AND country_id=52775;

/*
For example, if you wish to add a column group consisting of the cust_state_province and country_id columns to the customers table in SH schema:
*/


DECLARE
 cg_name VARCHAR2(30);
BEGIN
cg_name := dbms_stats.create_extended_stats(USER, 'customers', '(cust_state_province,country_id)');
END;
/

set autotrace traceonly

SELECT COUNT(*)
FROM customers 
WHERE cust_state_province = 'CA';

SELECT COUNT(*) 
FROM customers 
WHERE cust_state_province = 'CA' 
AND country_id=52790;

SELECT COUNT(*) 
FROM customers 
WHERE cust_state_province = 'CA' 
AND country_id=52775;
 
Demo

Histogram Demo
drop table servers purge;
drop table serv_inst purge;

@c: emp\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';
 
Related Topics
DBMS_STATS
Explain Plan
TK Prof & Trace
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [197 users online]    © 2010 psoug.org