Rem Filename   : indexqual.sql
Rem 
Rem Parameters : &1 = owner[.table(pattern)]
Rem              The default value for table(pattern) is %.
Rem
Rem Author     :
Rem
Rem Modification history
Rem --------------------
Rem 17-Sep-2005  V1.0  Initial creation.
Rem 25-Sep-2006  V1.1  Added column "# gets Full Index Scan", result is not correct, probably need to use the index size
Rem
Rem ==============================================================================================================================================
Rem
Rem Description : Lists an overview of the index quality properties. 
Rem
Rem                           Table name : The name of the table
Rem                           Index name : The name of the index
Rem                  Distinct Key Values : The number of distinct key values in the index. 
Rem                    Index Selectivity : 100 * ( Number of distinct key values / number of rows in the table )
Rem                    Clustering Factor : A indicator how badly rows with the same index key value are spread over multiple data blocks.
Rem                                        The larger the number, the worse the situation.
Rem               # gets Full Table Scan : The number of block gets needed to perform a full table scan, corresponds with the number of blocks in 
Rem                                        the table minus the empty blocks (=HWM).
Rem               # gets Full index Scan : The number of block gets needed to perform a full table scan, corresponds with the number of blocks in 
Rem                                        the table minus the empty blocks (=HWM).
Rem   Avg # gets Single key Index Access : The average number of gets performed to perform a full index scan.
Rem    Avg # gets Single key Index Onlys : The average number of gets performed to read all index leafs for a single key value. Oracle uses this
Rem                                        method if all queried information can be retrieved from the index itself without further accessing 
Rem                                        the table data segment.
Rem                   # gets WHEN EXISTS : The number of gets needed to resolve a "WHEN EXISTS" where condition for a single key value.
Rem
Rem
Rem Sample output
Rem -------------
Rem @indexqual est_core
Rem
Rem ----------------------------------------------------------------------------------------------------------------------------------------------
Rem                                   Index quality report for all indexes on tables matching pattern <EST_CORE.%>
Rem 
Rem                                                       of database PDB1 on server deeisep1
Rem ----------------------------------------------------------------------------------------------------------------------------------------------
Rem Current date and time : Wednesday 07-JUN-2006 16:40:31
Rem ----------------------------------------------------------------------------------------------------------------------------------------------
Rem 
Rem                                                                                                     # gets   Avg # gets Avg # gets
Rem                                                                 Distinct       Index Clustering       Full   Single key Single key      # gets
Rem Table name                     Index name                     Key values Selectivity     Factor Table Scan Index Access Index Only WHEN EXISTS
Rem ------------------------------ ------------------------------ ---------- ----------- ---------- ---------- ------------ ---------- -----------
Rem ..
Rem CO_PO_OPTIONS                  XIF1CO_PO_OPTIONS                   13666           2       6952       2659            4          3           3
Rem                                XIF2CO_PO_OPTIONS                     418           0     264570       2659          645         13           3
Rem                                XIF3CO_PO_OPTIONS                       6           0      12473       2659         2143         65           2
Rem                                XPKCO_PO_OPTIONS                   910303         100      21591       2659            4          3           3
Rem CO_PO_PART_INFOS               XAK1CO_PO_PART_INFOS              3393561         100      37137      34676            4          3           3
Rem                                XIF1CO_PO_PART_INFOS                13666           0      41417      34676            6          3           3
Rem                                XIF2CO_PO_PART_INFOS                 2086           0    1489735      34676          725         11           3
Rem                                XIF3CO_PO_PART_INFOS                  523           0    3389059      34676         6514         34           3
Rem                                XPKCO_PO_PART_INFOS               3393561         100      37137      34676            4          3           3
Rem ..
Rem ==============================================================================================================================================

SET PAGESIZE  0
SET TRIMSPOOL ON
SET VERIFY    off
SET TERMOUT   OFF
SET FEEDBACK  OFF

ALTER SESSION SET nls_numeric_characters = ",.";

-- Disect the input argument, and get the owner name and table pattern into two seperate 
-- substitution variables. The table pattern defaults to %.

DEFINE InputOwner       = ' '
DEFINE InputTableFilter = ' '
COLUMN table_owner NOPRINT NEW_VALUE InputOwner
COLUMN table_name  NOPRINT NEW_VALUE InputTableFilter

SELECT decode(instr('&&1','.'), 0, upper('&&1'),  /* Only the table owner was passed in. */
                                upper(substr('&&1',1,instr('&&1','.')-1))) table_owner,
       decode(instr('&&1','.'), 0, '%',           /* Only the table owner was passed in. */
                                upper(substr('&&1',instr('&&1','.')+1))) table_name
  FROM dual;


CLEAR COLUMNS
SET PAGESIZE   9999
SET TERMOUT    ON

@@header_title 153 DC 'Index quality report for all indexes on tables matching pattern <&InputOwner..&InputTableFilter>'

SET FEEDBACK   ON

COLUMN "TableName"      FORMAT A20         HEADING "Table name"
COLUMN "IndexName"      FORMAT A25        HEADING "Index name"
COLUMN "DistinctKeys"   FORMAT B999999999   HEADING "Distinct|Key values"
COLUMN "ClustFact"      FORMAT B999999999   HEADING "Clustering|Factor"
COLUMN "Selectivity"    FORMAT A11         HEADING "      Index|Selectivity"
COLUMN "HWM"            FORMAT 9999999     HEADING "# gets|Full|Table Scan"
COLUMN "LeafBlocks"     FORMAT 9999999     HEADING "# gets|Full|Index Scan"
COLUMN "AvgCostInd"     FORMAT B9999999     HEADING "Avg # gets|Single key|Index Access"
COLUMN "AvgCostIndOnly" FORMAT B9999999     HEADING "Avg # gets|Single key|Index Only"
COLUMN "CostIfExists"   FORMAT 9999999     HEADING "# gets|WHEN EXISTS"

BREAK ON "TableName" NODUP

set lines 300

SELECT i.table_name                                                 TableName,
       i.index_name                                                 IndexName,
       i.distinct_keys                                              DistinctKeys,
       CASE t.num_rows 
         WHEN 0 THEN '' 
         ELSE lpad(to_char((100*i.distinct_keys)/t.num_rows, '990'),11)
       END Selectivity,
       i.clustering_factor                                          ClustFact,
       t.blocks-t.empty_blocks                                      HWM,
       i.leaf_blocks                                                LeafBlocks,
       i.blevel+i.avg_leaf_blocks_per_key+avg_data_blocks_per_key   AvgCostInd,       
       i.blevel+i.avg_leaf_blocks_per_key                           AvgCostIndOnly,
       decode(i.avg_leaf_blocks_per_key,0,null,i.blevel)+1          CostIfExists,
	   to_char(i.last_analyzed,'ddmmyyyy hh24:mi:ss') last_analyzed
  FROM dba_indexes i,
       dba_tables t
 WHERE   i.table_owner   = '&InputOwner'
   AND   i.table_name like '&InputTableFilter'
   AND   t.owner         = i.table_owner   
   AND   t.table_name    = i.table_name
 ORDER BY i.table_name, i.index_name;

SELECT i.table_name                                                 TableName,
       i.index_name                                                 IndexName,
       i.distinct_keys                                              DistinctKeys,
       CASE t.num_rows 
         WHEN 0 THEN '' 
         ELSE lpad(to_char((100*i.distinct_keys)/t.num_rows, '990'),11)
       END Selectivity,
       i.clustering_factor                                          ClustFact,
	   i.blevel                                                     blevel,
	   s.bytes/1024/1024                                            MB,
	   to_char(o.last_ddl_time,'ddmmyyyy hh24:mi:ss')               last_ddl_time,
	   to_char(i.last_analyzed,'ddmmyyyy hh24:mi:ss') last_analyzed
  FROM dba_indexes i,
       dba_tables t,
	   dba_segments s,
	   dba_objects o
 WHERE   i.table_owner   = '&InputOwner'
   AND   i.table_name like '&InputTableFilter'
   AND   t.owner         = i.table_owner   
   AND   t.table_name    = i.table_name
   AND   s.owner = t.owner
   AND   s.segment_name = i.index_name
   AND   o.owner = t.owner
   AND   o.object_name = i.index_name
 ORDER BY i.table_name, i.index_name;

SET FEEDBACK ON
CLEAR BREAKS
CLEAR COLUMNS

UNDEFINE InputOwner
UNDEFINE InputTableFilter

