Oracle性能分析10：重建索引续-常用SQL

32e2

上一节讲了重建索引的原因和重建方法，这节继续介绍几个常用的SQL语句和存储过程。
查询所有失效的全局索引


    select index_name, status  
      from user_indexes  
     where table_name = upper(table_name)  
       and status = 'UNUSABLE'  

查询所有失效的分区索引


    select index_name, status  
      from user_ind_partitions  
     where index_name in (select index_name  
                            from user_indexes  
                           where table_name = upper(table_name)  
                             and partitioned = 'YES')  
       and status = 'UNUSABLE'  

在线重建所有的全局索引

下面的存储过程将在线重建指定表格的所有全局索引。


    declare  
      v_table_name varchar2(100) := upper(table_name);  
    begin  
      for i_index_name in (select index_name  
                             from user_indexes  
                            where table_name = upper(v_table_name)  
                              and partitioned = 'NO') loop  
          dbms_output.put_line(i_index_name.index_name);  
          execute immediate 'ALTER INDEX ' || i_index_name.index_name ||  
                            ' REBUILD ONLINE';  
      end loop;  
    end;  

在线重建所有失效的全局索引

下面的存储过程查看指定表格的所有全局索引，并在线重建所有失效的全局索引。


    declare  
      v_table_name varchar2(100) := upper(table_name);  
      v_status     varchar2(8);  
    begin  
      for i_index_name in (select index_name  
                             from user_indexes  
                            where table_name = upper(v_table_name)  
                              and partitioned = 'NO') loop  
        
        select status  
          into v_status  
          from user_indexes  
         where index_name = i_index_name.index_name;  
        if v_status = 'UNUSABLE' then  
          dbms_output.put_line(i_index_name.index_name);  
          execute immediate 'ALTER INDEX ' || i_index_name.index_name ||  
                            ' REBUILD ONLINE';  
        end if;  
        
      end loop;  
    end;  

在线重建所有的分区索引

下面的存储过程在线重建所有的分区索引。


    declare  
      v_table_name varchar2(100) := upper(table_name);  
    begin  
      FOR i_index_name IN (SELECT index_name  
                             FROM user_indexes  
                            WHERE table_name = upper(v_table_name)  
                              and partitioned = 'YES') LOOP  
        FOR i_partition_name IN (SELECT partition_name  
                                   FROM user_tab_partitions  
                                  WHERE table_name = upper(v_table_name)) LOOP  
          dbms_output.put_line(i_partition_name.partition_name || ' : ' ||  
                               i_index_name.Index_Name);  
          execute immediate 'ALTER INDEX ' || i_index_name.Index_Name ||  
                            ' REBUILD PARTITION ' ||  
                            i_partition_name.partition_name || ' ONLINE';  
        END LOOP;  
      END LOOP;  
    end;  

在线重建所有失效的分区索引

下面的存储过程查看指定表格的所有分区索引，依次遍历每个分区，并在线重建所有失效的分区索引。


    declare  
      v_table_name varchar2(100) := upper(table_name);  
      v_status     varchar2(8);  
    begin  
      FOR i_index_name IN (SELECT index_name  
                             FROM user_indexes  
                            WHERE table_name = upper(v_table_name)  
                              and partitioned = 'YES') LOOP  
        FOR i_partition_name IN (SELECT partition_name  
                                   FROM user_tab_partitions  
                                  WHERE table_name = upper(v_table_name)) LOOP  
          SELECT status  
            into v_status  
            FROM user_ind_partitions  
           WHERE index_name = i_index_name.index_name  
             and partition_name = i_partition_name.partition_name;  
          if v_status = 'UNUSABLE' THEN  
            dbms_output.put_line(i_partition_name.partition_name || ' : ' ||  
                                 i_index_name.Index_Name);  
            execute immediate 'ALTER INDEX ' || i_index_name.Index_Name ||  
                              ' REBUILD PARTITION ' ||  
                              i_partition_name.partition_name || ' ONLINE';  
          END IF;  
        END LOOP;  
      END LOOP;  
    end;  

也可以为上面重建索引使用nologging和compress（见重建索引）参数。