Oracle index info on table

Author : Daniel W. Fink




SET VERIFY OFF PAGESIZE 4000 LINESIZE 135
DEFINE owner_table = &1


COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT

SELECT 'tab_idx_info_hstry_'||UPPER('&&owner_table')||'_'||TO_CHAR(SYSDATE, 'YYYYMMDDhh24miss')||'.log' spoolname
FROM dual
/

SPOOL &&spool_name

COLUMN last_analyzed_time FORMAT A17 HEADING 'Analyzed Date'
COLUMN tab_degree FORMAT 9999 HEADING 'Deg'
COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn'
COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows'
COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks'
COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks'
COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct'
COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space'
COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length'
COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr'
COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A10 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A4 HEADING 'Null'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.999999999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A3 HEADING 'Hst'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'
COLUMN index_name FORMAT A30 HEADING 'Index Name'
COLUMN ind_status FORMAT A7 HEADING 'Status'
COLUMN ind_unique FORMAT A3 HEADING 'Unq'
COLUMN ind_blevel FORMAT 999 HEADING 'Lvl'
COLUMN ind_leafblocks FORMAT 999,999 HEADING 'Leaf Blks'
COLUMN ind_numrows FORMAT 999,999,999 HEADING 'Indx Rows'
COLUMN ind_distinctkeys FORMAT 999,999,999 HEADING 'Dstnct Keys'
COLUMN ind_clufac FORMAT 999,999,999 HEADING 'Clstr Fctr'
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN low_value FORMAT A20 HEADING 'Low Value'
COLUMN high_value FORMAT A20 HEADING 'High Value'
COLUMN tab_column_id NOPRINT

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT


SELECT TO_CHAR(t.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, TO_NUMBER(t.degree) tab_degree
, t.partitioned tab_partitioned
, t.num_rows tab_num_rows
, t.blocks tab_alloc_blocks
, (t.blocks - t.empty_blocks) tab_hwm_blocks
, ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2) tab_analyzed_pct
, t.avg_space tab_avg_space
, t.avg_row_len tab_avg_row_length
, t.monitoring tab_monitoring
FROM dba_tables t
WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
UNION ALL
SELECT TO_CHAR(th.analyzetime, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, NULL tab_degree
, NULL tab_partitioned
, th.rowcnt tab_num_rows
, th.blkcnt tab_alloc_blocks
, NULL tab_hwm_blocks
, ROUND((th.samplesize/DECODE(th.rowcnt,0,1,th.rowcnt))*100,2) tab_analyzed_pct
, NULL tab_avg_space
, th.avgrln tab_avg_row_length
, NULL tab_monitoring
FROM sys.wri$_optstat_tab_history th
WHERE th.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
ORDER BY last_analyzed_time DESC
/


PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
BREAK ON tab_column_name NODUP SKIP 1

SELECT tc.column_name tab_column_name
, TO_CHAR(tc.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, tc.data_type tab_column_datatype
, DECODE(tc.nullable, 'Y', NULL, tc.nullable) tab_column_nullable
, tc.num_distinct tab_column_numdistinct
, tc.density tab_column_density
, tc.num_nulls tab_column_numnulls
, DECODE(tc.histogram,'NONE',NULL,'FREQUENCY','FRQ',
'HEIGHT BALANCED','HGT',tc.histogram) tab_column_histogram
, TO_NUMBER(DECODE(tc.num_buckets,1,NULL,tc.num_buckets)) tab_column_numbuckets
, tc.column_id tab_column_id
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
UNION ALL
SELECT tc.column_name tab_column_name
, TO_CHAR(tch.timestamp#, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, NULL tab_column_datatype
, NULL tab_column_nullable
, tch.distcnt tab_column_numdistinct
, tch.density tab_column_density
, tch.null_cnt tab_column_numnulls
, NULL tab_column_histogram
, TO_NUMBER(DECODE(tchh.num_buckets,1,NULL,tchh.num_buckets)) tab_column_numbuckets
, tch.intcol# tab_column_id
FROM dba_tab_columns tc
, sys.wri$_optstat_histhead_history tch
, ( SELECT tchh2.intcol#
, tchh2.savtime
, count(tchh2.bucket) num_buckets
FROM sys.wri$_optstat_histgrm_history tchh2
WHERE tchh2.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
GROUP BY tchh2.intcol#
, tchh2.savtime
) tchh
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
AND tch.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
AND tch.intcol# = tc.column_id
AND tch.intcol# = tchh.intcol# (+)
AND tch.savtime = tchh.savtime (+)
ORDER BY tab_column_id, last_analyzed_time DESC, tab_column_name
/



PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
BREAK ON index_name NODUP SKIP 1 ON ind_unique NODUP

SELECT i.index_name index_name
, TO_CHAR(i.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, i.status ind_status
, DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique
, i.blevel ind_blevel
, i.leaf_blocks ind_leafblocks
, i.num_rows ind_numrows
, i.distinct_keys ind_distinctkeys
, i.clustering_factor ind_clufac
FROM dba_indexes i
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
UNION ALL
SELECT i.index_name index_name
, TO_CHAR(ih.analyzetime, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, NULL ind_status
, DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique
, ih.blevel ind_blevel
, ih.leafcnt ind_leafblocks
, ih.rowcnt ind_numrows
, ih.distkey ind_distinctkeys
, ih.clufac ind_clufac
FROM dba_indexes i
, sys.wri$_optstat_ind_history ih
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
AND ih.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER(i.owner||'.'||i.index_name)
AND o.object_type = 'INDEX'
)
ORDER BY ind_unique, index_name, last_analyzed_time DESC
/

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT

BREAK ON index_name NODUP SKIP 1 ON column_name NODUP

WITH col_hi_lo_vals AS
( SELECT tc.column_name
, tc.data_type
, TO_CHAR(tc.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, tc.low_value raw_low_value
, tc.high_value raw_high_value
, SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
, SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
UNION ALL
SELECT tc.column_name
, tc.data_type
, TO_CHAR(tch.timestamp#, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, tch.lowval raw_low_value
, tch.hival raw_high_value
, SUBSTR(dump(tch.lowval), (INSTR(dump(tch.lowval),': ')+2)) date_low_val
, SUBSTR(dump(tch.hival), (INSTR(dump(tch.hival),': ')+2)) date_high_val
FROM dba_tab_columns tc
, sys.wri$_optstat_histhead_history tch
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
AND tch.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
AND tch.intcol# = tc.column_id
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, last_analyzed_time
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second
ELSE
NULL
END low_date
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second
ELSE
NULL
END high_date
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_low_value)
ELSE
NULL
END low_num
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_high_value)
ELSE
NULL
END high_num
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_low_value)
ELSE
NULL
END low_char
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_high_value)
ELSE
NULL
END high_char
FROM col_hi_lo_vals
)
SELECT ic.index_name,
ic.column_name
, chlvt.last_analyzed_time
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.low_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.low_char
END low_value
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.high_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.high_char
END high_value
FROM dba_ind_columns ic
, col_hi_lo_vals_translated chlvt
WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table')
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position, chlvt.last_analyzed_time DESC
/

SPOOL off










apt-rdbms-01.aptus.co.uk:APTDB\sys> @index scott.emp





*********************************************************************************
Table Statistics for scott.emp
*********************************************************************************


Allocated HWM Analyze Avg Block Avg Row
Analyzed Date Deg Prtn Rows Blocks Blocks Pct Free Space Length Mntr
----------------- ----- ---- ------------ ------------ ------------ ------- ---------- ------- ----
2011/07/07 22:00 1 NO 14 5 5 100.00 0 38 YES


*********************************************************************************
Column Statistics for scott.emp
*********************************************************************************


Distinct Number
Column Name Analyzed Date Datatype Null Values Density of Nulls Hst Buckets
------------------------------ ----------------- ---------- ---- ------------ ------------ ------------ --- --------
EMPNO 2011/07/07 22:00 NUMBER N 14 .071428571 0

ENAME 2011/07/07 22:00 VARCHAR2 14 .071428571 0

JOB 2011/07/07 22:00 VARCHAR2 5 .200000000 0

MGR 2011/07/07 22:00 NUMBER 6 .166666667 1

HIREDATE 2011/07/07 22:00 DATE 13 .076923077 0

SAL 2011/07/07 22:00 NUMBER 12 .083333333 0

COMM 2011/07/07 22:00 NUMBER 4 .250000000 10

DEPTNO 2011/07/07 22:00 NUMBER 3 .035714286 0 FRQ 3



*********************************************************************************
Index Statistics for scott.emp
*********************************************************************************


Index Name Analyzed Date Status Unq Lvl Leaf Blks Indx Rows Dstnct Keys Clstr Fctr
------------------------------ ----------------- ------- --- ---- --------- ------------ ------------ ------------
PK_EMP 2011/07/07 22:00 VALID Y 0 1 14 14 1



*********************************************************************************
Index Columns for scott.emp
*********************************************************************************


Index Name Column Name Analyzed Date Low Value High Value
------------------------------ ------------------------------ ----------------- -------------------- --------------------
PK_EMP EMPNO 2011/07/07 22:00 7369 7934