Oracle Performance Analysis 10: Re-indexing-common SQL

Oracle Performance Analysis 10: Re-indexing-common SQL

The previous section describes the causes and methods for re-indexing. This section describes several common SQL statements and stored procedures.

Query all invalid global indexes

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

Query all invalid partition Indexes

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'

Rebuilding all global indexes online

The following stored procedure recreates all global indexes of a specified table online.

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;

Rebuilding all invalid global indexes online

The following stored procedures view all global indexes of a specified table and recreate all invalid global indexes online.

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;

Rebuilding all partition indexes online

The following stored procedure re-creates all the partition indexes online.

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;

Rebuilding all invalid partition indexes online

In the following stored procedure, you can view all the partition indexes of a specified table, traverse each partition in sequence, and recreate all invalid partition indexes online.

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;

You can also use the nologging and compress parameters for the preceding re-indexing (see re-indexing.