177 lines
7.1 KiB
SQL
177 lines
7.1 KiB
SQL
|
|
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Statistics of a table (including part, sub-par, ind, ind-part, ind-subpart)
|
|
* Parameters : 1 - OWNER (% - wildchar, \ - escape char, default is '%')
|
|
* 2 - Table Name (% - wildchar, \ - escape char, default is '%')
|
|
* 3 - Column Name (% - wildchar, \ - escape char, default is '%')
|
|
* 4 - Partition Name (% - wildchar, \ - escape char, default is '%')
|
|
* 5 - Sub-Partition Name (% - wildchar, \ - escape char, default is '%')
|
|
* 6 - object_type (% - wildchar, \ - escape char, default is 'TABLE')
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 30-Jul-15 Vishal Gupta Increased size of SampleSize column
|
|
* 01-Oct-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE column_name
|
|
UNDEFINE partition_name
|
|
UNDEFINE subpartition_name
|
|
UNDEFINE object_type
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE column_name="&&3"
|
|
DEFINE partition_name="&&4"
|
|
DEFINE subpartition_name="&&5"
|
|
DEFINE object_type="&&6"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _column_name NEW_VALUE column_name NOPRINT
|
|
COLUMN _partition_name NEW_VALUE partition_name NOPRINT
|
|
COLUMN _subpartition_name NEW_VALUE subpartition_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
set term off
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
, DECODE('&&column_name','','%',UPPER('&&column_name')) "_column_name"
|
|
, DECODE('&&partition_name','','%',UPPER('&&partition_name')) "_partition_name"
|
|
, DECODE('&&subpartition_name','','%',UPPER('&&subpartition_name')) "_subpartition_name"
|
|
, DECODE('&&object_type','','TABLE',UPPER('&&object_type')) "_object_type"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * C O L U M N S T A T I S T I C S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Table Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Column Name = '&&column_name'
|
|
PROMPT * - Partition Name = '&&partition_name'
|
|
PROMPT * - SubPartition Name = '&&subpartition_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT ***********************************************************************
|
|
|
|
COLUMN object_name HEADING "TableName" FORMAT a45
|
|
COLUMN Object_type HEADING "Object|Type" FORMAT a8
|
|
COLUMN column_name HEADING "ColumnName" FORMAT a25
|
|
COLUMN global_stats HEADING "G|l|o|b|a|l" FORMAT a1 TRUNCATE
|
|
COLUMN last_analyzed HEADING "LastAnalyzed" FORMAT a18
|
|
COLUMN user_stats HEADING "U|s|e|r" FORMAT a1 TRUNCATE
|
|
COLUMN Col_Size_MB HEADING "Size (MB)" FORMAT 999,999
|
|
COLUMN sample_size HEADING "SampleSize" FORMAT 999,999,999,999
|
|
COLUMN avg_col_len HEADING "Avg|Col|Len" FORMAT 999
|
|
COLUMN num_distinct HEADING "NumDistinct" FORMAT 99,999,999,999
|
|
COLUMN num_nulls HEADING "NumNulls" FORMAT 9,999,999,999
|
|
COLUMN density HEADING "Density" FORMAT 9.999999999999999
|
|
COLUMN histogram HEADING "Histogram" FORMAT a15
|
|
COLUMN num_buckets HEADING "Num|Buc|ket" FORMAT 999
|
|
|
|
SELECT 'TABLE' object_type
|
|
, s.owner || '.' || s.table_name object_name
|
|
, s.column_name
|
|
, TO_CHAR(s.last_analyzed,'DD-MON-YY HH24:Mi:SS') last_analyzed
|
|
, s.global_stats
|
|
, s.user_stats
|
|
, ROUND(( (t.num_rows - s.num_nulls) * (s.avg_col_len)) / power(1024,2)) Col_Size_MB
|
|
, s.sample_size
|
|
, s.avg_col_len
|
|
, s.num_distinct
|
|
, s.num_nulls
|
|
--, s.low_value
|
|
--, s.high_value
|
|
, s.density
|
|
, s.histogram
|
|
, s.num_buckets
|
|
FROM dba_tab_statistics t
|
|
JOIN dba_tab_col_statistics s ON s.owner = t.owner AND s.table_name = t.table_name AND t.partition_name IS NULL
|
|
WHERE t.owner LIKE '&&owner' ESCAPE '\'
|
|
AND t.table_name LIKE '&&table_name' ESCAPE '\'
|
|
AND s.column_name LIKE '&&column_name' ESCAPE '\'
|
|
AND 'TABLE' LIKE '&&object_type' ESCAPE '\'
|
|
/*
|
|
UNION ALL
|
|
SELECT 'PART' object_type
|
|
, s.owner || '.' || s.table_name
|
|
|| NVL2(s.partition_name,':'||s.partition_name,'') object_name
|
|
, s.column_name
|
|
, TO_CHAR(s.last_analyzed,'DD-MON-YY HH24:Mi:SS') last_analyzed
|
|
, s.global_stats
|
|
, s.user_stats
|
|
, ROUND(( (t.num_rows - s.num_nulls) * (s.avg_col_len)) / power(1024,2)) Col_Size_MB
|
|
, s.sample_size
|
|
, s.avg_col_len
|
|
, s.num_distinct
|
|
, s.num_nulls
|
|
--, s.low_value
|
|
--, s.high_value
|
|
, ROUND(s.density,-9) density
|
|
, s.histogram
|
|
, s.num_buckets
|
|
FROM dba_part_col_statistics s
|
|
WHERE s.owner LIKE '&&owner' ESCAPE '\'
|
|
AND s.table_name LIKE '&&table_name' ESCAPE '\'
|
|
AND s.column_name LIKE '&&column_name' ESCAPE '\'
|
|
AND NVL(s.partition_name,'%') LIKE '&&partition_name' ESCAPE '\'
|
|
AND 'PARTITION' LIKE '&&object_type' ESCAPE '\'
|
|
UNION ALL
|
|
SELECT 'SUBPART' object_type
|
|
, s.owner || '.' || s.table_name
|
|
|| NVL2(s.subpartition_name,':'||s.subpartition_name,'')
|
|
object_name
|
|
, s.column_name
|
|
, TO_CHAR(s.last_analyzed,'DD-MON-YY HH24:Mi:SS') last_analyzed
|
|
, s.global_stats
|
|
, s.user_stats
|
|
, s.sample_size
|
|
, s.avg_col_len
|
|
, s.num_distinct
|
|
, s.num_nulls
|
|
--, s.low_value
|
|
--, s.high_value
|
|
, ROUND(s.density,-9) density
|
|
, s.histogram
|
|
, s.num_buckets
|
|
FROM dba_subpart_col_statistics s
|
|
WHERE s.owner LIKE '&&owner' ESCAPE '\'
|
|
AND s.table_name LIKE '&&table_name' ESCAPE '\'
|
|
AND s.column_name LIKE '&&column_name' ESCAPE '\'
|
|
AND NVL(s.subpartition_name,'%') LIKE '&&subpartition_name' ESCAPE '\'
|
|
AND 'SUBPARTITION' LIKE '&&object_type' ESCAPE '\'
|
|
*/
|
|
ORDER BY object_name
|
|
, column_name
|
|
/
|
|
|
|
|
|
|
|
|
|
@@footer
|