/*
Program : get_table_index_info.sql
Purpose : Get current optimizer statistics for a table
Author : Daniel W. Fink OptimalDBA.com
Created : March 17, 2009
Update :
Parameters : &1 owner_table in the format owner.table (not case sensitive)
: Example @get_table_index_info.sql scott.emp
Exit Code : Not Used
Comments :
Disclaimer : No warranty is provided for any use of the script, statements or logic included.
This script, statements and logic are for personal use only and may not be included
as part of a commercial product.
Please address any comments to script_feedback@optimaldba.com
This comment block and all lines above must be included.
*/
SET VERIFY OFF PAGESIZE 400 LINESIZE 190 FEEDBACK OFF NUMWIDTH 15
DEFINE owner_table = &1
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT
SELECT ‘tab_idx_info_’||UPPER(‘&&owner_table’)||’_’||TO_CHAR(SYSDATE, ‘YYYYMMDD’)||’.lst’ spoolname
FROM dual
/
SPOOL &&spool_name
COLUMN tab_degree FORMAT 9999 HEADING ‘Deg’
COLUMN tab_partitioned FORMAT A4 HEADING ‘Prtn’
COLUMN tab_num_rows FORMAT 999,999,999,999 HEADING ‘Rows’
COLUMN tab_alloc_blocks FORMAT 999,999,999,999 HEADING ‘Allocated|Blocks’
COLUMN tab_hwm_blocks FORMAT 999,999,999,999 HEADING ‘HWM|Blocks’
COLUMN tab_last_analyzed_time FORMAT A17 HEADING ‘Analyzed Date’
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’
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
SELECT 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,
TO_CHAR(t.last_analyzed, ‘MM/DD/YYYY hh24:mi’) tab_last_analyzed_time,
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’)
ORDER BY t.table_name
/
COLUMN tab_column_name FORMAT A30 HEADING ‘Column Name’
COLUMN tab_column_datatype FORMAT A20 HEADING ‘Datatype’
COLUMN tab_column_nullable FORMAT A10 HEADING ‘Nullable?’
COLUMN tab_column_numdistinct FORMAT 999,999,999,999 HEADING ‘Distinct|Values’
COLUMN tab_column_density FORMAT 9.99999 HEADING ‘Density’
COLUMN tab_column_numnulls FORMAT 999,999,999,999 HEADING ‘Number|of Nulls’
COLUMN tab_column_histogram FORMAT A16 HEADING ‘Histogram’
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING ‘Buckets’
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
SELECT tc.column_name tab_column_name,
tc.data_type tab_column_datatype,
DECODE(tc.nullable, ‘N’, ‘NOT NULL’, NULL) 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, tc.histogram) tab_column_histogram,
TO_NUMBER(DECODE(tc.num_buckets,1,NULL,
tc.num_buckets)) tab_column_numbuckets
FROM dba_tab_columns tc
WHERE tc.owner||’.’||tc.table_name = UPPER(‘&&owner_table’)
ORDER BY tc.column_id
/
COLUMN ind_name FORMAT A30 HEADING ‘Index Name’
COLUMN ind_status FORMAT A10 HEADING ‘Status’
COLUMN ind_unique FORMAT A1 HEADING ‘U’
COLUMN ind_blevel FORMAT 999 HEADING ‘BLvl’
COLUMN ind_leafblocks FORMAT 999,999,999 HEADING ‘Leaf|Blocks’
COLUMN ind_num_rows FORMAT 999,999,999,999 HEADING ‘Rows’
COLUMN ind_distinctkeys FORMAT 999,999,999,999 HEADING ‘Distinct|Keys’
COLUMN ind_clufac FORMAT 999,999,999,999 HEADING ‘Clustering|Factor’
COLUMN ind_last_analyzed_time FORMAT A17 HEADING ‘Analyzed Date’
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
SELECT i.index_name ind_name,
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_num_rows,
i.distinct_keys ind_distinctkeys,
i.clustering_factor ind_clufac,
TO_CHAR(i.last_analyzed, ‘MM/DD/YYYY hh24:mi’) ind_last_analyzed_time
FROM dba_indexes i
WHERE i.table_owner||’.’||i.table_name = UPPER(‘&&owner_table’)
ORDER BY i.uniqueness DESC, i.index_name
/
COLUMN index_name FORMAT A30 HEADING ‘Index Name’
COLUMN column_name FORMAT A30 HEADING ‘Column Name’
COLUMN low_value FORMAT A60 HEADING ‘Low Value’
COLUMN high_value FORMAT A60 HEADING ‘High Value’
BREAK ON index_name NODUP
PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT
WITH col_hi_lo_vals AS
( select tc.column_name
, tc.data_type
, 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’)
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, 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
, 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
/
SPOOL off