Check for Histograms on User Objects
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 /