Check for Histograms on User Objects
Table of Contents

12c Onwards

--
-- Check for Histograms on User Objects 12c onwards
--
 
SET PAUSE ON
SET PAUSE 'Press return to Continue'
SET LINESIZE 300
SET PAGESIZE 60
 
COL owner FOR A30
COL table_name FOR A30
COL column_name FOR A30
COL num_distinct 9999
COL density FOR 99
COL num_nulls FOR 99999
COL num_buckets FOR 999
COL histogram FOR A10
 
SELECT
  owner,
  table_name,
  column_name,
  num_distinct,
  density,
  num_nulls,
  num_buckets,
  histogram
FROM
  all_tab_col_statistics
WHERE 
  owner = upper('&Enter_Object_Owner')
ORDER BY 
  table_name, column_name
/


Pre 12c

--
-- Check for Histograms on User Objects pre-12c
--
-- Count of 0 = No Histograms
-- Count of 1 = A single Value Column
-- Count of 2 = High and Low Endpoint Values 
-- Count gt 2 = Histogram Buckets
--
 
SET PAUSE ON
SET PAUSE 'Press return to Continue'
SET LINESIZE 300
SET PAGESIZE 60
 
COL owner FOR A30
COL table_name FOR A30
COL column_name FOR A30
 
SELECT 
  owner
  table_name,
  column_name, count(*) 
FROM
  dba_tab_histograms 
WHERE owner = upper('&Enter_Object_Owner')
GROUP BY owner,table_name, column_name 
ORDER BY count(*) ASC
/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License