#!/usr/bin/ksh
# program smenu_desc_idx.sh
# Author : Bernard Polarski : 07/12/99
#                             rewritten 23 september 2005
#                             Bart Debersaques : Add size on index
#                             bpa              : Add count on unusable indexes
#                             bpa              : add -inv options for quick list all invalids
#set -x
    #--------------- comments section ---------------------
    # This script is part of the module 3 (DB utilities)
    # in smenu
    # Purpose : List all indexes for a user
    # a table
    #--------------- Environement section ---------------------
    #
    SBIN2=$SBIN/module2
    SBINS=$SBIN/scripts
    HOST=`hostname`
    #--------------- Test variables section ---------------------
    PROMPT="
prompt .            idx -u <OWNER>      [-c]       All table/indexes for the schema
prompt .            idx -t <TABLE_NAME> [-s][-c]   -s for size(m)  -c count unusable sub indx
prompt .            idx -i <INDEX_NAME> [-p [-w]]  -p show sub part -w row count -g show stats
prompt .            idx -h for a more detailed help
prompt .            U = Unique    P=Degree of parallelism   S=Status
prompt
"
    function help
    {
    cat <<EOF

       Usage  : idx -u [owner]  -i [index -p|-w] -t [table -s] -h -cf -fb
                idx -u [owner]  -cf
                idx -u [owner]  -c

           -u  <OWNER>      : List all index in the schema sorted by table
                                add -c to list indexes invalid for the user : idx -u SCOTT -c
           -t <table_name>  : List all index for a table
                                   -s  display the size info or
                                   -c  display unusable sub index part count
                               ie) idx -t <table> -s
                                   idx -t <table> -c
           -i <index_name>  : -p  will add index sub partitions info such as status, tbs and size for each sub
                                  idx -i <index_name> -p
                                  -w to replace tbs with rows info
                                     idx -i <index_name> -p -w
                              -fb will add the text of the function based index
         -ix                : Alternate info on index from ixora
         -inv               : List invalid indexes, partitions and subpartitions, order by owner
        -sinv               : Generate Rebuild statement for ll invalid indexes, partitions and subpartitions
         -cf                : Show clustering factor in percentage. The lower is the best, 100% is the worst
         -hg                : Show system overal histograms distribution among indexes


Notes:
Clustering factor : - if near the number of blocks, then the table is ordered : index entries in a single leaf block
                      tend to point to rows in same data block
                    - if near the number of rows, the table is randomly ordered : index entries in a single
                      leaf block are unlikely to point to rows in same data block

Global stats      :   For partitioned indexes, YES means statistics are collected for the INDEX as a whole
                      NO means statistics are estimated from statistics on underlying index partitions or subpart.
Pct_direct_access :   For secondary indexes on IOTs, rows with VALID guess

EOF
   }
TR=${TR:-UCB}
if [ "x-$1" = "x-" ];then
     help
     exit
fi
VAR_FIELD="to_char(LAST_ANALYZED,'DD-MM HH24:MI') la,"
CF_FIELD=" clustering_factor cf, "
TIT_IDX_NAME_LEN=28
TIT_COL_NAME=20
TIT_TABLE_NAME=22
while [ -n "$1" ]
    do
      case $1 in
         -h ) help
              exit ;;
         -ix )  typeset -u var=$2; INDEX=$var; IX=TRUE; shift;;
         -hg )  HISTO=TRUE;;
         -i )  typeset -u var=$2
                INDEX=$var
                shift;;
         -cf) CF_FIELD=" round(decode(b.num_rows,0,0,((CLUSTERING_FACTOR/(select blocks from dba_Tables
                           where table_name ='&table_name' and table_owner='&table_owner' ))
                           /(b.NUM_ROWS/(select blocks from dba_Tables
                           where table_name =upper('&table_name') and table_owner=upper('&table_owner') ))))*100,1) pct ,"
              BLOCKS="c.blocks,"
              #AND_TABLES=" c.table_name = b.table_name and c.owner=b.table_owner and"
              DBA_TABLES=",dba_tables c "
              TIT_IDX_NAME_LEN=24
              TIT_COL_NAME=17
              TIT_TABLE_NAME=23;;
         -w ) PAR_SQL1="nvl(b.num_rows,a.num_rows) sel ,
                        nvl(b.distinct_keys,a.distinct_keys) dstk," ;;
         -p ) PAR_SQL1="lpad(nvl(b.tablespace_name,a.tablespace_name),25) tablespace_name, "
              PAR_SQL0="
set head on
break on partition_position on partition_name on status on report on tablespace_name
COL sel       FORMAT  999999999 heading 'Num rows'
COL dstk       FORMAT  999999999 heading 'Distinct rows'
COL partition_position       FORMAT  9999 heading 'Part| Pos'
COL fsize            FORMAT  9999999   heading 'Size (m)'
COL partition_name           FORMAT  A25 head 'Partition name'
COL subpartition_name        FORMAT  A25 head 'System generated|Subartition name'
COL tablespace_name          FORMAT  A25   justify c HEAD ' Tablespace name'
col last_analyzed            format  A18  head 'Last Analyzed'
col status format  A8
comp sum of fsize on report
select a.partition_position, a.partition_name,  b.subpartition_name,
       substr(nvl(b.status,a.status),1,6) status,"

       PAR_SQL2=" (nvl(b.leaf_blocks,a.leaf_blocks) * &fsize )/1024/1024 fsize,
       to_char(nvl(b.last_analyzed,a.last_analyzed),'DD-MM-YY HH24:MI') la
from dba_ind_partitions a, dba_ind_subpartitions b
          where a.index_name = '$INDEX' and
                a.index_name = b.index_name (+) and
                a.partition_name=b.partition_name  (+)"
        PAR_SQL3=" order by a.partition_name;
"
;;
         -v ) set -x;;
         -t ) typeset -u var=$2
              TABLE=" a.table_name ='$var' and "
              shift;;
         -s ) VAR_FIELD="(select sum(bytes/1024/1024) bytes from dba_segments
                  where segment_name = b.index_name and owner = b.owner group by owner,segment_name) bytes,"
              ;;
         -c ) VAR_FIELD=" (select count(1) cpt from dba_ind_subpartitions where status = 'UNUSABLE'
                            and index_name = b.index_name) +
                           (select count(1) cpt from dba_ind_partitions where status = 'UNUSABLE'
                                 and index_name = b.index_name) un ,"
              # only limit selection to unusable index when '-u' is selected, not for -t
              if [ -z "$TABLE" ];then
                 LIMIT0='select * from ('
                 LIMIT1=" ) where un > 0"
              fi
              ;;
         -u  ) OW1=$2
              shift
            if [ "x-$TR" = "x-" ];then
                 TR=`grep "^TR=" $SBIN/smenu.env | cut -f2 -d"="`
            fi
            if [ "x-$TR" = "x-UCB" ] ;then
                     OWNER="'`echo $OW1 | tr '[a-z]' '[A-Z]'`'"
            else
                     OWNER="'`echo $OW1 | tr a-z A-Z`'"
            fi
            OTHERWISE=" a.table_owner=$OWNER and "
            AND_OWNER=" and a.table_owner = $OWNER"
            AND_OWNER0=" and SEGMENT_owner = $OWNER"
            AND_I_OWNER=" and a.index_owner = $OWNER"
            AND_OOWNER=" and o.owner = $OWNER"
            ;;
         -inv ) LIST_INVALID=TRUE;;
         -sinv ) REBUILD_INVALID=TRUE;;
         -fb )  PAR_SQL0="select COLUMN_POSITION, COLUMN_EXPRESSION from
                     DBA_IND_EXPRESSIONS a where INDEX_NAME = '$INDEX' $AND_OWNER;";;
         * ) help
             exit ;
      esac
      shift
    done
    if [ -n "$PAR_SQL0" ];then
        PAR_SQL="$PAR_SQL0
                 $PAR_SQL1 
                 $PAR_SQL2 $AND_I_OWNER $PAR_SQL3"
    fi
    #--------------- Process section ---------------------
    #--------------- Get system password section ---------------------
    . $SBIN/scripts/passwd.env
    . ${GET_PASSWD}
    if [  "x-$CONNECT_STRING" = "x-" ];then
          echo "could no get a the password of $S_USER"
          exit 0
    fi
    #--------------- Process section ---------------------

#................................................
# list invalid indexes
#................................................
if [  "$HISTO" = "TRUE" ];then
sqlplus -s  "$CONNECT_STRING" <<EOF

set termout off verify off feed off lines 132 pages 66
select  sum(case when max_cnt > 2 then 1 else 0 end) histograms,
                sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms
    from (
        select table_name, max(cnt) max_cnt
                from (
                        select table_name, column_name, count(*) cnt
                                from dba_tab_histograms
                                group by table_name, column_name
                ) group by table_name
   )
/
EOF

#................................................
# list invalid indexes
#................................................
elif [  "$IX" = "TRUE" ];then
INDEX=`echo $INDEX| $NAWK '{print (toupper($1))}'`
#-------------------------------------------------------------------------------
#--
#-- Script:     index_access_paths.sql
#-- Purpose:    *** called from table_access_paths.sql ***
#--
#-- Copyright:  (c) Ixora Pty Ltd
#-- Author:     Steve Adams (http://www.ixora.com.au/scripts/sql/index_access_paths.sql)
#-- Adapted to Smenu by B. Polarski
#--------------------------------------------------------------------------------
#       cat <<EOF
#MACHINE $HOST - ORACLE_SID : $ORACLE_SID                                     Page:   1
#Date              -  `date +%a`    `date +%d' '%B' '%Y' '%H:%M:%S`
#Username          -  $S_USER
#Index List - Index stats for $INDEX
#EOF

sqlplus -s  "$CONNECT_STRING" <<EOF

set termout off verify off feed off
col value new_value BlkSz noprint
col object_id new_value idx_id noprint
col table_name new_value table_name noprint
col iotbit new_value IotBit noprint
col rpb new_value RowsPerBlock noprint

select value from v\$parameter where name = 'db_block_size'
/
select o.object_id from dba_objects o where o.object_type='INDEX' and o.object_name = '$INDEX' $AND_OOWNER
/
select a.table_name from dba_indexes a where a.index_name = '$INDEX' $AND_OWNER
/
select decode(bitand(t.property, 64), 64, 'YES', 'NO')  iotbit from tab\$ t, obj\$ o
       where o.obj# =  t.obj# and o.type# = 2 and o.name = '&table_name'  $AND_OOWNER
/
select (1-(a.avg_space/&BlkSz))* round(nvl(floor(&BlkSz - 66 - a.INI_TRANS * 24)/greatest(a.AVG_ROW_LEN + 2, 11), 1),0)  rpb
  from dba_tables a where a.table_name = '&table_name' $AND_OWNER
/
set termout on
set heading on pause off  feed off  verify off pagesize 56 linesize 132

column blocks         format 999999999
column density        format 9999999
column key_values     format 99999999999
column entries        format 99999999999 justify right
column table_ordering format 990.9999 head "ordering"

select /*+ ordered */
  o.name  index_name, i.leafcnt blocks,
  (
    least( 1, (
        select
          (
            sum(
              decode(sign(ic.pos# - nvl(i.spare2, 0)), 1, h.avgcln + 0.5, 0)
            ) + 11
          ) * i.rowcnt +
          (
            sum(
              decode(sign(nvl(i.spare2, 0) - ic.pos#), 1, h.avgcln + 0.5, 0)
            ) + 11
          ) * i.leafcnt
        from
          sys.icol\$  ic,
          sys.hist_head\$  h
        where
          ic.obj# = i.obj# and
          h.obj# (+) = ic.bo# and
          h.intcol# (+) = ic.intcol#
      ) / (i.leafcnt * (&BlkSz - 108 - i.initrans * 24))
    )
  )  density,
  i.distkey  key_values,
  i.rowcnt  entries,
  decode(
    '&IotBit',
    'YES',
    '           n/a',
    (
      round(1 - (i.clufac - i.rowcnt / &RowsPerBlock + 1) / i.rowcnt / (1 - 1 / &RowsPerBlock),4)
    )
  )  table_ordering
from
  sys.obj\$  o,
  sys.ind\$  i
where
  o.obj# = &idx_id and
  i.obj# = o.obj#
/

column storage         format a10
column bytes           format 99999 justify right
column buckets         format 999999
column popular         format a7
column non_pop_density format a15

select
  tc.name  column_name,
  decode(sign(ic.pos# - nvl(i.spare2, 0)), 1, 'NORMAL', 'COMPRESSED')  storage,
  h.avgcln  bytes,
  h.bucket_cnt  buckets,
  decode(
    h.bucket_cnt,
    1,
    null,
    decode(
      1 + h.bucket_cnt - h.row_cnt,
      0,
      '    nil',
      ((1 + h.bucket_cnt - h.row_cnt) / h.bucket_cnt)
    )
  )  popular,
  decode(
    sign(h.density * i.rowcnt - least(100, greatest(i.rowcnt/100, 10))),
    -1,
    lpad(to_char(h.density * i.rowcnt, '999') || decode(round(h.density * i.rowcnt), 1, '  row', ' rows'), 15)
  )  non_pop_density
from
  sys.ind\$  i,
  sys.icol\$  ic,
  sys.col\$  tc,
  sys.hist_head\$  h
where
  i.obj# = &idx_id and
  ic.obj# = i.obj# and
  tc.obj# = ic.bo# and
  tc.intcol# = ic.intcol# and
  h.obj# (+) = tc.obj# and
  h.intcol# (+) = tc.intcol#
order by
  ic.pos#
/


EOF
#................................................
elif [  "$REBUILD_INVALID" = "TRUE" ];then
       cat <<EOF
MACHINE $HOST - ORACLE_SID : $ORACLE_SID                                     Page:   1

Date              -  `date +%a`    `date +%d' '%B' '%Y' '%H:%M:%S`
Username          -  $S_USER
List invalid index,  unusable index partitions and subpartitions

EOF
sqlplus -s  "$CONNECT_STRING" <<EOF

set heading on pause off
set pagesize 56
set linesize 132
select 'alter index ' || owner||'.'|| index_name || ' rebuild online ;'
             from dba_indexes where status != 'VALID' and status != 'N/A'
union
select 'alter index ' ||  index_owner ||'.'|| index_name || ' rebuild partition ' || partition_name || ' ;'
              from dba_ind_partitions where status != 'USABLE' and status != 'N/A'
union
select  'alter index ' || index_owner ||'.'|| index_name || ' rebuild  subpartition ' || subpartition_name || ' ;'
            from dba_ind_subpartitions where status != 'USABLE' and status != 'N/A'
/
EOF
#................................................
elif [  "$LIST_INVALID" = "TRUE" ];then
       cat <<EOF
MACHINE $HOST - ORACLE_SID : $ORACLE_SID                                     Page:   1

Date              -  `date +%a`    `date +%d' '%B' '%Y' '%H:%M:%S`
Username          -  $S_USER
List invalid index,  unusable index partitions and subpartitions

EOF
sqlplus -s  "$CONNECT_STRING" <<EOF

set heading on pause off
set pagesize 56
set linesize 132
select owner, index_name, ' ' partition_name,status from dba_indexes where status != 'VALID' and status != 'N/A'
union
select  index_owner owner, index_name, partition_name, status from dba_ind_partitions where status != 'USABLE' and status != 'N/A'
union
select  index_owner owner, index_name, subpartition_name, status from dba_ind_subpartitions where status != 'USABLE' and status != 'N/A'
/
EOF
#................................................
# for all index
#................................................
elif [ -z "$INDEX" ];then
       cat <<EOF
MACHINE $HOST - ORACLE_SID : $ORACLE_SID                                     Page:   1
Date              -  `date +%a`    `date +%d' '%B' '%Y' '%H:%M:%S`
Username          -  $S_USER
Table List - Table/Index Cross Reference for $OWNER

EOF
sqlplus -s  "$CONNECT_STRING" <<EOF
set heading on pause off pagesize 56 linesize 150
column dg format A2 heading 'P '
column f1 format a$TIT_IDX_NAME_LEN heading 'Index|Name'
column f3 format a$TIT_COL_NAME heading 'Column|Name'
column f0 format a$TIT_TABLE_NAME new_value the_table heading 'Table|Name'
column tablespace_name format a20 heading 'Tablespace'
column f2 format a1 heading 'U'
col cf format 999999999 head 'Clust| Factor'
col bytes format 99999999 head 'Size(m)'
col dk format 999999999 head 'Distinct| Keys'
col la format A11  head 'last analysed'
col st format A1  head 'S'
col un format  999999  head 'Unusable|Sub indx'
col status format a8
col meg format 9999999 head 'Size(m)'
col pct format 990.9 head "cf(%)"
column blocks format 9999999 head "Table|blocks" justify c

col db_block_size new_value db_block_size noprint
set head off feed off
select value db_block_size from v\$parameter where name = 'db_block_size';
set head on feed on verify off
break on f0 on fw skip 1 on f0 on ft on f1 on f2

$PROMPT
$LIMIT0
select b.table_name f0,
       b.index_name f1,
       substr(uniqueness,1,1)   f2,
       column_name  f3, clustering_factor cf,
       decode(b.num_rows,0,0,((CLUSTERING_FACTOR/c.blocks)/(b.NUM_ROWS/c.blocks))*100) pct,
       $BLOCKS DISTINCT_KEYS dk,
       b.NUM_ROWS, 
       leaf_blocks*&db_block_size/1048576 meg,
       substr(b.status,1,1) st,
       substr(b.DEGREE,1,2) dg
from dba_ind_columns a, dba_indexes b, dba_Tables c
where $OTHERWISE  $TABLE
    a.index_name = b.index_name            and
    a.index_owner = b.table_owner and
    a.table_name = b.table_name $AND_OWNER
    and  c.table_name = b.table_name and c.owner=b.table_owner
order
   by b.table_type, b.table_name, b.index_name, column_position $LIMIT1
/
exit
EOF

else # if for a single index

       cat <<EOF
MACHINE $HOST - ORACLE_SID : $ORACLE_SID                                     Page:   1
Date              -  `date +%a`    `date +%d' '%B' '%Y' '%H:%M:%S`
Username          -  $S_USER
Table List - Index stats for $INDEX

EOF

sqlplus -s "$CONNECT_STRING" <<EOF

set heading on pause off  feed off  verify off pagesize 56 linesize 132
/
col db_block_size new_value fsize noprint
set head off feed off
select value db_block_size from v\$parameter where name = 'db_block_size'
/
set head on
column  table_name  new_value  table_name
column  table_owner new_value  table_owner

column dg format A2 heading 'P '
column table_name heading 'Table,name'
column table_owner heading 'Table owner'
column f1 format a32 heading 'Index Name'
column f2 format a1 heading 'U'
column f3 format a24 heading 'Column|Name'
column descend format a4 heading 'Ord'
column cp format 999 heading 'Col|Pos'
col cf format 9999999999 head 'Clust| Factor'
col pct format 990.9 head "cf(%)"
col dk format 9999999999 head 'Distinct| Keys'
col num_rows format 9999999999 head 'Num rows'
col la format A14  head 'last analysed'
col sel format 99999999.9  head 'Selectivity' justify c
col  AVG_DATA_BLOCKS_PER_KEY format 999999 head 'Avg data| Blocks | Per Key'
col  AVG_LEAF_BLOCKS_PER_KEY format 999999 head 'Avg leaf| Blocks | Per Key'
col FREELISTS format 99 head 'Free|List'
col FREELIST_GROUPS format 99 head 'Free| List|Group'
col bytes format 99999999 head 'Size(m)' justify c
col USER_STATS format A5 head 'user |Stat' justify c
col GLOBAL_STATS format A6 head 'Global |Stats' justify c
col partitioned format A4 head 'Part'
col PCT_DIRECT_ACCESS format 999999 head '  Pct |Direct|Access' justify c
col Blevel format 999 head 'Blevel' justify c
col leaf_blocks format 99999999 head 'Leaf|Blocks' justify c
col compression format A8 head 'Compress' justify c
col typ format A8 head 'Index|type' justify c
col pct_free format 9999 head 'Pct|Free' justify c
col st format A1  head 'S'
         -fb  )
prompt .            If you have a \$ in name type \\\$
$PROMPT
select a.table_name, a.table_owner ,a.tablespace_name from dba_indexes a where a.index_name = '$INDEX' $AND_OWNER
/

break on f1 on uq on cf on dk on num_rows on st on la on dg on sel

select a.index_name f1, substr(uniqueness,1,1)  f2,
       column_name  f3, COLUMN_POSITION cp, descend,
       substr(status,1,1) st,
       substr(DEGREE,1,2) dg,
       PARTITIONED ,GLOBAL_STATS,  USER_STATS,
       PCT_FREE, FREELISTS, FREELIST_GROUPS,
       PCT_DIRECT_ACCESS
from dba_ind_columns a, dba_indexes b
where  a.index_name      = '$INDEX'            and
    a.table_owner      = '&table_owner'       and
    a.index_owner      = '&table_owner'      and
    b.table_owner      = '&table_owner'      and
    a.index_name       = b.index_name        and
    a.table_name       = '&table_name'
order
   by b.index_name, column_position
/
break on compression
prompt

select  Blevel, index_type typ, LEAF_BLOCKS,
        AVG_DATA_BLOCKS_PER_KEY , AVG_LEAF_BLOCKS_PER_KEY , $CF_FIELD
        DISTINCT_KEYS dk,
        b.NUM_ROWS,
        (select sum(bytes/1024/1024) bytes from dba_segments where segment_name = '$INDEX' and owner = '&table_owner' ) bytes,
        decode(nvl(distinct_keys,0),0,0,b.num_rows/distinct_keys) sel,
        to_char(b.LAST_ANALYZED,'DD-MM-YY HH24:MI') la , compression
from dba_indexes b
where  b.index_name       = '$INDEX'            and
       b.table_owner      = '&table_owner'      and
       b.owner            = '&table_owner'      and
       b.table_name       = '&table_name'
/
prompt
$PAR_SQL
EOF

fi