oracle索引重建

set heading off
set feedback off
set linesize 200
column c_sql format a200
spool c:\c_index.sql
select 'drop index ' || index_name || ';'
  from user_indexes
 where table_owner = 'RTMART'
   and index_type = 'NORMAL'
   and (table_name like 'LOAD%' or table_name like 'TAC%' or
       table_name like 'TAG%' or table_name like 'TBU%' or
       table_name like 'TCO%' or table_name like 'TCS%' or
       table_name like 'TCQ%' or table_name like 'TPL%' or
       table_name like 'T_SM%');
select 'create index ' || iname || ' on ' || tname || '(' || cname || ');' c_sql
  from (select index_name iname,
               table_name tname,
               case max(rnumber)
                when 1 then
                 max(decode(rnumber, 1, column_name, ''))
                when 2 then
                 max(decode(rnumber, 1, column_name, '')) || ',' ||
                 max(decode(rnumber, 2, column_name, ''))
                when 3 then
                 max(decode(rnumber, 1, column_name, '')) || ',' ||
                 max(decode(rnumber, 2, column_name, '')) || ',' ||
                 max(decode(rnumber, 3, column_name, ''))
                else
                 max(decode(rnumber, 1, column_name, '')) || ',' ||
                 max(decode(rnumber, 2, column_name, '')) || ',' ||
                 max(decode(rnumber, 3, column_name, '')) || ',' ||
                 max(decode(rnumber, 4, column_name, ''))
               end as cname
          from (select index_name,
                       table_name,
                       column_name,
                       row_number() over(partition by index_name order by table_name, column_name) rnumber
                  from (select t.index_name, t.table_name, t.column_name
                          from user_ind_columns t, user_indexes b
                         where t.index_name = b.index_name
                           and t.table_name = b.table_name
                           and b.index_type = 'NORMAL'

                           and b.table_owner = 'RTMART'
                           and (t.table_name like 'LOAD%' or
                               t.table_name like 'TAC%' or
                               t.table_name like 'TAG%' or
                               t.table_name like 'TBU%' or
                               t.table_name like 'TCO%' or
                               t.table_name like 'TCS%' or
                               t.table_name like 'TCQ%' or
                               t.table_name like 'TPL%' or
                               t.table_name like 'T_SM%')
                         order by table_name, index_name, column_name))
         group by index_name, table_name);
spool off
set heading on
set feedback on