column tabname format a25
column indname format a25
column index_cols format a35
select a.tabname, a.indname, a.index_cols, b.indname, b.index_cols from
   ( select tabname, indname,
            max(decode(p, 1,     c,NULL)) ||
            max(decode(p, 2,', '||c,NULL)) ||
            max(decode(p, 3,', '||c,NULL)) ||
            max(decode(p, 4,', '||c,NULL)) ||
            max(decode(p, 5,', '||c,NULL)) ||
            max(decode(p, 6,', '||c,NULL)) ||
            max(decode(p, 7,', '||c,NULL)) ||
            max(decode(p, 8,', '||c,NULL)) ||
            max(decode(p, 9,', '||c,NULL)) ||
            max(decode(p,10,', '||c,NULL)) ||
            max(decode(p,11,', '||c,NULL)) ||
            max(decode(p,12,', '||c,NULL)) ||
            max(decode(p,13,', '||c,NULL)) ||
            max(decode(p,14,', '||c,NULL)) ||
            max(decode(p,15,', '||c,NULL)) ||
            max(decode(p,16,', '||c,NULL)) index_cols
       from (select a1.table_owner||'.'||a1.table_name tabname, a1.index_name||nvl2(a2.constraint_type,' ('||a2.constraint_type||')',null) indname, substr(column_name,1,30) c, column_position p
                  from dba_ind_columns a1, dba_constraints a2 
                 where a1.table_owner=a2.owner(+) and a1.index_name=a2.index_name(+))
      group by tabname, indname  ) A,
   ( select tabname, indname,
            max(decode(p, 1,     c,NULL)) ||
            max(decode(p, 2,', '||c,NULL)) ||
            max(decode(p, 3,', '||c,NULL)) ||
            max(decode(p, 4,', '||c,NULL)) ||
            max(decode(p, 5,', '||c,NULL)) ||
            max(decode(p, 6,', '||c,NULL)) ||
            max(decode(p, 7,', '||c,NULL)) ||
            max(decode(p, 8,', '||c,NULL)) ||
            max(decode(p, 9,', '||c,NULL)) ||
            max(decode(p,10,', '||c,NULL)) ||
            max(decode(p,11,', '||c,NULL)) ||
            max(decode(p,12,', '||c,NULL)) ||
            max(decode(p,13,', '||c,NULL)) ||
            max(decode(p,14,', '||c,NULL)) ||
            max(decode(p,15,', '||c,NULL)) ||
            max(decode(p,16,', '||c,NULL)) index_cols
       from (select b1.table_owner||'.'||b1.table_name tabname, b1.index_name||nvl2(b2.constraint_type,' ('||b2.constraint_type||')',null) indname, substr(column_name,1,30) c, column_position p
                  from dba_ind_columns b1, dba_constraints b2 
                 where b1.table_owner=b2.owner(+) and b1.index_name=b2.index_name(+))
      group by tabname, indname  ) B
   where a.tabname = b.tabname
     and a.indname <> b.indname
     and a.index_cols like b.index_cols || ',%'
order by tabname
/
