Database Tuning‎ > ‎

How can I determine for which tables histograms are created?

posted Sep 13, 2010, 12:54 PM by Sachchida Ojha
The following statement delivers all tables on which histograms are created (due to the restrictions described above, histograms are not taken into account in columns with only one distinct value):

SELECT OWNER, TABLE_NAME FROM DBA_TAB_COLUMNS
WHERE NUM_BUCKETS > 1
GROUP BY OWNER, TABLE_NAME
UNION
SELECT OWNER, TABLE_NAME FROM DBA_PART_COL_STATISTICS
WHERE NUM_BUCKETS > 1
GROUP BY OWNER, TABLE_NAME;

As of Oracle 10g, you can also use the following query:

SELECT OWNER, TABLE_NAME FROM DBA_TAB_COLUMNS
WHERE  HISTOGRAM != 'NONE'
GROUP BY OWNER, TABLE_NAME
UNION
SELECT OWNER, TABLE_NAME FROM DBA_PART_COL_STATISTICS
WHERE  HISTOGRAM != 'NONE'
GROUP BY OWNER, TABLE_NAME;

Comments