How to make indexes UNUSABLE

The following sample procedure makes indexes UNUSABLE.

This can be helpful when trying to load many thousands or millions of records in a table and you want to avoid index maintenance in order to speed up the loading process.

Make sure to set SKIP_UNUSABLE_INDEXES to TRUE before trying to insert into any table with UNUSABLE indexes. This can be done by including the following statement in your PL/SQL code:

EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';

This procedure can handle both partitioned and non-partitioned indexes.

   PROCEDURE set_indexes_unusable
   AS
      idxs       pkg_mig_exec.vtable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (256);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' unusable'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    table_name IN
                  ('TABLE1', 'TABLE2', 'TABLE3')
      AND    partitioned = 'NO'
      AND    uniqueness <> 'UNIQUE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

-- Handle partitioned indexes

      SELECT index_owner || '.' || index_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions
      WHERE  index_owner = 'MY_SCHEMA' AND partition_name = 'PARTITION_A';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         stmnt :=
            'alter index ' || idxs (i)
            || ' modify partition PARTITION_A unusable';
         pkg_mig_utils.log_info ('Executing : ' || stmnt);

         EXECUTE IMMEDIATE stmnt;
      END LOOP;
   END;

Unusable indexes must be rebuilt in order to become usable again.

PROCEDURE rebuild_unusable_indexes
   AS
      idxs       stmnttable;
      tot_idxs   PLS_INTEGER;
      stmnt      VARCHAR2 (1024);
   BEGIN
      SELECT 'alter index ' || owner || '.' || index_name || ' rebuild'
      BULK COLLECT INTO idxs
      FROM   all_indexes
      WHERE  owner = 'MY_SCHEMA'
      AND    status = 'UNUSABLE'
      AND    partitioned = 'NO';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;

      SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || partition_name
      BULK COLLECT INTO idxs
      FROM   all_ind_partitions a
      WHERE  index_owner = 'MY_SCHEMA'
      AND    partition_name = 'PARTITION_A'
      AND    status = 'UNUSABLE';

      tot_idxs := SQL%ROWCOUNT;

      FOR i IN 1 .. tot_idxs
      LOOP
         pkg_mig_utils.log_info ('Executing: ' || idxs (i));

         EXECUTE IMMEDIATE idxs (i);
      END LOOP;
   END;