rem =========================================================================
rem 
rem                     idxaudit.sql
rem 
rem     Copyright (C) Oriole Software, 1998
rem 
rem     Downloaded from http://www.oriolecorp.com
rem 
rem     This script for Oracle database administration is free software; you
rem     can redistribute it and/or modify it under the terms of the GNU General
rem     Public License as published by the Free Software Foundation; either
rem     version 2 of the License, or any later version.
rem 
rem     This script is distributed in the hope that it will be useful,
rem     but WITHOUT ANY WARRANTY; without even the implied warranty of
rem     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
rem     GNU General Public License for more details.
rem 
rem     You should have received a copy of the GNU General Public License
rem     along with this program; if not, write to the Free Software
rem     Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem 
rem =========================================================================
rem
rem    This script produces a report about all indexes, which
rem    indicates selectivity (globally and, for concatenated
rem    indexes, column per column).
rem    The purpose is to identify useless indexes (7 distinct
rem    values or less) and poorly designed concatenated indexes.
rem
rem    IMPORTANT : WE ASSUME STATISTICS ARE UP TO DATE.
rem
rem    It must be run from a DBA account.
rem    Two of the three queries hereafter work with both Oracle7 and Oracle8.
rem    The second one however is Oracle-version specific. The uncommented
rem    version is the Oracle8 one, and the Oracle7 one is also provided as a
rem    comment.
rem
set linesize 80
set recsep off
set verify off
set feedback off
set echo off
set pagesize 0
set trimspool on
set heading off
set newpage 0
rem
rem   Get the database name and date of the report
rem
column current_date noprint new_value today
column dbname noprint new_value mydb
select name dbname,
       sysdate current_date
from v$database;
set pagesize 60
spool idxaudit.log
ttitle LEFT today center 'Audit of indexes' right mydb skip 1
rem
rem   Changing, commenting out or removing the btitle  would be a breach of
rem   copyright
rem
btitle skip 1 LEFT 'Generated by idxaudit.sql (c) Oriole Software 1999/www.oriolecorp.com' RIGHT SQL.PN
O
rem
set pagesize 999
set heading on
set numwidth 8
clear breaks
rem
rem   This query lists the indexes which are probably useless.
rem   As a rule of thumb, an ordinary (as opposed to bitmap)
rem   index is useless (i.e. the index access is slower than
rem   the table scan) if a key value returns more than 12 to
rem   15% of the rows (the bigger the table, the smaller the
rem   percentage). We assume statistics have been collected
rem   fairly recently.
rem   The query selects indexes with less than 7 distinct values,
rem   which should be questioned.
rem   However they can be acceptable :
rem      - If the index is a bitmap index
rem      - If the distribution of values is such that
rem        those used in queries are quite selective
rem      - If the columns indexed belong to a foreign
rem        key constraint and if this and the referenced
rem        table can be concurrently updated.
rem   
column "TABLE" format A35
column "USEFUL INDEX?" format A25
break on "TABLE" on "ROWS"
select u.name || '.' || ot.name "TABLE",
       t.rowcnt "ROWS",
       oi.name "USEFUL INDEX?",
       i.distkey "KEYS"
from sys.user$ u,
     sys.tab$ t,
     sys.obj$ ot,
     sys.ind$ i,
     sys.obj$ oi
where ot.owner# = u.user#
  and i.bo# = ot.obj#
  and nvl(t.rowcnt, 0) > 100
  and i.bo# = t.obj#
  and i.distkey < 7
  and oi.obj# = i.obj#
  and oi.owner# != 0
order by 1
/
rem
rem   Questionable concatenated indexes.
rem
rem   It is recommended that in concatenated indexes
rem   the most selective columns (those with the highest
rem   number of distinct values) come first. The query
rem   lists all the concatenated indexes for which a column
rem   has more distinct values than a column which precedes
rem   it.
rem
rem   Before deciding to rebuild the index the other way round,
rem   one must remember that Oracle can use the index for queries
rem   in which only some of the columns in the index are referenced
rem   in the WHERE clause if these columns are the first ones only.
rem   To be more precise, let's say that column C1 has 2,000
rem   distinct values and column C2 80,000. In theory, a concatenated
rem   index on both columns should be built on (C2, C1). This is fine
rem   if both columns appear in the WHERE clause. If, however, there
rem   is a number of queries with a condition on C1 alone, the index
rem   cannot be used whereas and index on (C1, C2) could be. If there
rem   is a significant number of queries with a condition on C2 alone
rem   too, then one have two indexes, one on (C2, C1) usable by queries
rem   involving both columns or C2 alone, and a second one on C1 alone.
rem   If you have either C1 and C2 or C1 alone appearing in the
rem   condition, then an index on (C1, C2) is justified.
rem
column "TABLE" format A30
column "POS" format 9999 heading 'GOOD | POS ?'
column "INDEX" format A18
column "COLUMN" format A15
set num 8
break on "TABLE" on "INDEX"
rem
rem     Oracle7 version
rem
--V7 select u.name || '.' || ot.name "TABLE",
--V7        oi.name "INDEX",
--V7        ic.pos# "POS",
--V7        c.name "COLUMN",
--V7        c.distcnt "DIST VAL"
--V7 from sys.user$ u,
--V7      sys.obj$ ot,
--V7      sys.obj$ oi,
--V7      sys.col$ c,
--V7      sys.icol$ ic,
--V7      sys.ind$ i
--V7 where i.cols > 1
--V7   and i.obj# = ic.obj#
--V7   and i.bo# = ic.bo#
--V7   and i.bo# = c.obj#
--V7   and ic.col# = c.col#
--V7   and ot.obj# = i.bo#
--V7   and oi.obj# = i.obj#
--V7   and u.user# = ot.owner#
--V7   and exists (select null
--V7               from sys.icol$ ic2,
--V7                    sys.col$ c2,
--V7                    sys.icol$ ic3,
--V7                    sys.col$ c3
--V7               where ic2.obj# = ic.obj#
--V7                 and ic2.bo# = ic.bo#
--V7                 and ic2.bo# = c2.obj#
--V7                 and ic3.obj# = ic.obj#
--V7                 and ic3.bo# = ic.bo#
--V7                 and ic3.bo# = c3.obj#
--V7                 and ic2.col# = c2.col#
--V7                 and ic3.col# = c3.col#
--V7                 and ic2.pos# > ic3.pos#
--V7                 and c2.distcnt > c3.distcnt)
--V7 order by 1, 2, 3
--V7 /
rem
rem     Oracle8 version
rem
select u.name || '.' || ot.name "TABLE",
       oi.name "INDEX",
       ic.pos# "POS",
       c.name "COLUMN",
       h.distcnt "DIST VAL"
from sys.user$ u,
     sys.obj$ ot,
     sys.obj$ oi,
     sys.hist_head$ h,
     sys.col$ c,
     sys.icol$ ic,
     sys.ind$ i
where i.cols > 1
  and i.obj# = ic.obj#
  and i.bo# = ic.bo#
  and i.bo# = c.obj#
  and ic.col# = c.col#
  and i.bo# = h.obj#
  and ic.col# = h.col#
  and ot.obj# = i.bo#
  and oi.obj# = i.obj#
  and u.user# = ot.owner#
  and exists (select null
              from sys.icol$ ic2,
                   sys.hist_head$ h2,
                   sys.icol$ ic3,
                   sys.hist_head$ h3
              where ic2.obj# = ic.obj#
                and ic2.bo# = ic.bo#
                and ic2.bo# = h2.obj#
                and ic3.obj# = ic.obj#
                and ic3.bo# = ic.bo#
                and ic3.bo# = h3.obj#
                and ic2.col# = h2.col#
                and ic3.col# = h3.col#
                and ic2.pos# > ic3.pos#
                and h2.distcnt > h3.distcnt)
order by 1, 2, 3
/
rem
rem     As Oracle is able to use a concatenated index if only
rem   the first columns in the index are referenced in the WHERE
rem   clause, it is totally useless to also have single-column
rem   indexes on those columns - it just adds overhead during DML
rem   operations.
rem
select u1.name || '.' || o1.name || ' redundant with ' || u2.name
               || '.' || o2.name "REDUNDANT INDEXES"
from sys.user$ u1,
     sys.obj$ o1,
     sys.ind$ i1,
     sys.icol$ ic1,
     sys.user$ u2,
     sys.obj$ o2,
     sys.ind$ i2,
     sys.icol$ ic2
where o1.obj# = i1.obj#
  and o1.owner# != 0
  and o1.owner# = u1.user#
  and o2.obj# = i2.obj#
  and o2.owner# != 0
  and o2.owner# = u2.user#
  and i1.bo# = i2.bo#
  and i1.cols = 1
  and i2.cols > 1
  and ic1.obj# = i1.obj#
  and ic2.obj# = i2.obj#
  and ic1.bo# = i1.bo#
  and ic2.bo# = i2.bo#
  and ic1.col# = ic2.col#
  and ic1.pos# = ic2.pos#
/
spool off