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

上一節講了重建索引的原因和重建方法，這節繼續介紹幾個常用的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（見重建索引）參數。