Database Tuning‎ > ‎

How can I determine whether histograms are created for a table?

posted Sep 13, 2010, 12:57 PM by Sachchida Ojha
SELECT COLUMN_NAME, NUM_BUCKETS FROM DBA_TAB_COLUMNS WHERE
OWNER = '<table_owner>' AND TABLE_NAME = '<table_name>'
AND NUM_BUCKETS > 1;

 Note that columns with only one distinct value only ever have one bucket, even if histograms are created. This means that the SELECT described above is not 100% correct. For Oracle 9i and lower, there is no further information about DBA or V$ views.

As of Oracle 10g and higher, you can read the existence directly from the HISTOGRAM column: 
  •  
    SELECT COLUMN_NAME, HISTOGRAM, NUM_BUCKETS FROM DBA_TAB_COLUMNS WHERE
       OWNER = '<table_owner>' AND TABLE_NAME = '<table_name>';

Partitioned tables:

SELECT COLUMN_NAME, PARTITION_NAME, NUM_BUCKETS
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = '<table_owner>' AND TABLE_NAME = '<table_name>'
AND NUM_BUCKETS > 1;

 Note the restriction described above for columns that have only one distinct value.

Oracle 10g and higher:


SELECT COLUMN_NAME, PARTITION_NAME, HISTOGRAM, NUM_BUCKETS
FROM DBA_PART_COL_STATISTICS
WHERE OWNER = '<table_owner>' AND TABLE_NAME = '<table_name>'
ORDER BY 1, 2;
Comments