@@header


/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Similar Tables indexes
*  Parameters : 1 - Owner      (Use % as wild card, \ as ESCAPE)
*               2 - TableName  (Use % as wild card, \ as ESCAPE)
*                                   
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  25-Sep-12  Vishal Gupta  Created
*
*/

/*

PROMPT *************************
PROMPT Similar Indexes
PROMPT *************************

column column_name format a30
break on TABLE_OWNER on TABLE_NAME skip 1
BREAK ON INDEX_NAME 

select TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION 
FROM   ALL_IND_COLUMNS 
WHERE   (TABLE_OWNER, TABLE_NAME, INDEX_NAME ) IN
(SELECT TABLE_OWNER
      , TABLE_NAME
      , INDEX_NAME
FROM    ALL_IND_COLUMNS ind
WHERE   COLUMN_POSITION = 1
AND     TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','XDB','WMSYS','SYSMAN')
AND    (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN  (SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME
                                                   FROM   (SELECT TABLE_OWNER , TABLE_NAME, COLUMN_NAME,
                                                                  COUNT (*) TCOUNT
                                                           FROM   ALL_IND_COLUMNS
                                                           WHERE  COLUMN_POSITION = 1
                                                           AND    TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','XDB','WMSYS','SYSMAN')
                                                           GROUP  BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME
                                                           HAVING COUNT (*) > 1
                                                           )
                                                   )
)
ORDER BY 1,2,3,5                                                   
/
 
*/


/************************************
*  INPUT PARAMETERS
************************************/
DEFINE owner="&&1"
DEFINE table_name="&&2"

COLUMN  _owner           NEW_VALUE owner            NOPRINT
COLUMN  _table_name      NEW_VALUE table_name       NOPRINT
COLUMN  _object_type     NEW_VALUE object_type      NOPRINT


set term off
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 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"
FROM DUAL;
set term on


PROMPT *************************
PROMPT Similar Indexes
PROMPT *************************

COLUMN  owner                            HEADING "Index Owner"              FORMAT a20
COLUMN  table_name                       HEADING "Table Name"               FORMAT a40
COLUMN  index_owner                      HEADING "Index Owner"              FORMAT a20
COLUMN  index_name                       HEADING "Index Name"               FORMAT a40
COLUMN  index_type                       HEADING "Index Type"               FORMAT a10
COLUMN  status                           HEADING "Status"                   FORMAT a8
COLUMN  visibility                       HEADING "Visible"                  FORMAT a10
COLUMN  uniqueness                       HEADING "Uniqueness"               FORMAT a15
COLUMN  Columns                          HEADING "Index columns"            FORMAT a90 

BREAK ON TABLE_NAME

WITH Indexes as 
(
SELECT /*+  */ 
       i.table_owner || '.' || i.table_name  table_name
     , i.owner       || '.' || i.index_name  index_name
     , i.uniqueness
     , i.index_type
     --, i.status
     --, i.visibility
     , SUBSTR(SYS_CONNECT_BY_PATH ( ic.column_name || DECODE(ic.descend,'ASC','',' (desc) ') , ' , '  ),4) Columns
FROM  dba_indexes i
    , dba_ind_columns ic
    , dba_ind_expressions ie
WHERE i.owner            = ic.index_owner
  AND i.index_name       = ic.index_name
  AND i.table_owner      = ic.table_owner
  AND i.table_name       = ic.table_name
  AND ic.index_owner     = ie.index_owner     (+)
  AND ic.index_name      = ie.index_name      (+)
  AND ic.table_owner     = ie.table_owner     (+)
  AND ic.table_name      = ie.table_name      (+)
  AND ic.column_position = ie.column_position (+)
  AND i.table_owner     LIKE upper('&&owner') ESCAPE '\'
  AND i.table_name      LIKE upper('&&table_name') ESCAPE '\'
  AND CONNECT_BY_ISLEAF = 1
  AND LEVEL = ic.column_position
CONNECT BY ic.table_owner         = PRIOR ic.table_owner
       AND ic.table_name          = PRIOR ic.table_name
       AND ic.index_owner         = PRIOR ic.index_owner
       AND ic.index_name          = PRIOR ic.index_name
       AND ic.column_position - 1 = PRIOR ic.column_position 
START WITH ic.table_owner LIKE upper('&&owner') ESCAPE '\'
       AND ic.table_name  LIKE upper('&&table_name') ESCAPE '\'
)
, indexes_similar as
(
SELECT a.*
  FROM indexes a
WHERE EXISTS (SELECT 1 from indexes b
               WHERE b.table_name  = a.table_name 
                 AND b.columns LIKE a.columns || '%' 
                 AND LENGTH(b.columns) > LENGTH(a.columns) 
              )
)
SELECT i.*
  FROM indexes i
     , indexes_similar s
WHERE i.table_name = s.table_name
  AND i.columns like s.columns   || '%'   
ORDER BY i.table_name
       , i.index_name
       , i.uniqueness 
       
;

UNDEFINE owner
UNDEFINE table_name

 
@@footer