Oracle Performance Analysis 10: Rebuilding indexes continued-common SQL

The previous section describes the reasons for rebuilding the index and how to rebuild it, and this section continues with several common SQL statements and stored procedures.

Querying for all defunct global indexes

Select Index_name, status from  user_indexes WHERE table_name = UPPER (table_name) and   status = ' unusable '

Querying for all defunct 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 rebuilds all global indexes for the specified table online.

DECLARE  v_table_name VARCHAR2 (+): = 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;

Online rebuilding of all failed global indexes

The following stored procedure looks at all global indexes for the specified table and rebuilds all the failed global indexes online.

DECLARE  v_table_name VARCHAR2 (+): = 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 of your partitioned indexes online

The following stored procedure rebuilds all the partition indexes online.

DECLARE  v_table_name VARCHAR2 (+): = 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 failed partition indexes online

The following stored procedure looks at all the partition indexes of the specified table, loops through each partition, and rebuilds all the failed partition indexes online.

DECLARE v_table_name varchar2 (+): = 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_parti Tion_name in (SELECT partition_name from User_tab_partitions WH ERE table_name = UPPER (V_table_name)) LOOP SELECT status into V_status from User_ind_partitions W      Here 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 nologging and compress (see Rebuild Index) parameters for the above rebuild index.

Oracle Performance Analysis 10: Rebuilding indexes continued-common SQL