Moving, Rebuilding and or Resizing Indexes

As this procedure deals only with indexes, I consider it a very low risk process. The main source of risk if any is if your target tablespace for the index is either low on available space or is very fragmented. Just because the index was removed from a tablespace does not mean it can be recreated in the same tablespace. Unless there is sufficient free space and or contiguous extents in the target tablespace the index may not recreate.

Why Rebuild ?
The reasons for moving and or resizing indexes are relatively self explanatory, however, it is worth explaining why you would want to regularly rebuild some indexes.
When records are added to a table, the table and index grows by the number of records added. When records are deleted from a table the records are physically deleted from the table but are logically deleted from the index.
Table Index
Task count count
------------------------------------
Add 100 Records 100 100
Delete 100 Records 0 100
Add 100 Records 100 200
delete 100 Records 0 200
As you can see from the above example, the index will continue to grow, where as the table may remain static.

How to identify indexes with "excessive" logically deleted rows.
The first place to look is at any dynamic table I.E. Interface and temporary tables.
Once the target tables have been identified, obtain a list of index names by using the following example SQL*PLUS script.
SELECT index_name
FROM dba_indexes
WHERE owner = 'SCOTT'
and table_name = 'EMP'
/
For each of the indexes identified, execute the validate index command followed by the SQL*PLUS script shown below to obtain the results of the validate index command. Note: The index_stats table holds only one row.
SQL> validate index gl.gl_interface_n1
/

Index analyzed.

SQL>
Use the following SQL*PLUS script to obtain the results.
column A heading "Index Name" format a30
column B heading "Rows" format 999,999,990
column C heading "Deleted|Rows" format 999,999,990
column D heading "% Del" format 990.0

SELECT name A,
lf_rows B,
del_lf_rows C,
(del_lf_rows * 100 ) / lf_rows D
FROM index_stats
/
I usually rebuild indexes that have more than 20 % logically deleted rows.

Rebuilding an index.
The following PL*SQL script will build an SQL*PLUS script to drop and then recreate the index.
Note: The storage clause and the index owner's password in the output file will have to be modified before you execute the resulting script. Modifying the storage clause will allow you to resize and or move the index.


clear columns

set serveroutput on;
set echo off
set heading off
set feedback off
set verify off

WHENEVER SQLERROR CONTINUE

/**************************************/
/** OBTAIN COMMAND LINE PARAMETERS **/
/** OWNER = Index owner **/
/** INDEX_NAME = Index name **/
/**************************************/

DEFINE owner = '&1'
DEFINE index_name = '&2'

DECLARE
/**************************************/
/** CURSOR C1 Get the index columns **/
/**************************************/
CURSOR C1 is
SELECT column_name
FROM dba_ind_columns
WHERE index_owner = upper('&&owner')
and index_name = upper('&&index_name')
ORDER by column_position;

/**************************************/
/** Decalre variables **/
/**************************************/
d_column_name VARCHAR(30);
d_table_name VARCHAR(30);
d_uniqe VARCHAR(9);
d_max_columns NUMBER(4) := 0;
d_column_counter NUMBER(4) := 0;

BEGIN

dbms_output.put_line('/****************************************/' );
dbms_output.put_line('/* Drop and create index script */' );
dbms_output.put_line('/* */' );
dbms_output.put_line('/* Dont forget to edit this file for: */' );
dbms_output.put_line('/* */' );
dbms_output.put_line('/* 1. Connect password */' );
dbms_output.put_line('/* 2. Storage params */' );
dbms_output.put_line('/* */' );
dbms_output.put_line('/****************************************/' );
dbms_output.put_line('');
dbms_output.put_line('');

dbms_output.put_line('connect &&owner/password' );
dbms_output.put_line('');
dbms_output.put_line('');

dbms_output.put_line('DROP INDEX &&index_name;' );
dbms_output.put_line('');
dbms_output.put_line('');

/**************************************/
/** Determine the number of columns **/
/** in the index **/
/**************************************/

SELECT max(column_position)
INTO d_max_columns
FROM dba_ind_columns
WHERE index_owner = upper('&&owner')
and index_name = upper('&&index_name');

SELECT table_name, uniqueness
INTO d_table_name, d_uniqe
FROM dba_indexes
WHERE owner = upper('&&owner')
and index_name = upper('&&index_name');

/**************************************/
/** Determine if the index is uniqe **/
/**************************************/
SELECT uniqueness
INTO d_uniqe
FROM dba_indexes
WHERE owner = upper('&&owner')
and index_name = upper('&&index_name');

dbms_output.put_line('CREATE '|| d_uniqe ||' INDEX &&index_name' );
dbms_output.put_line('ON '||d_table_name );
dbms_output.put_line('(');

OPEN C1;
LOOP
FETCH C1 INTO d_column_name;
d_column_counter := d_column_counter + 1;
EXIT WHEN C1%NOTFOUND;
IF d_column_counter < d_max_columns THEN
dbms_output.put_line( d_column_name||',' );
ELSE
dbms_output.put_line( d_column_name||' )' );
END IF;
END LOOP;

dbms_output.put_line('STORAGE ( initial X M next X M');
dbms_output.put_line(' minextents 1 maxextents 50');
dbms_output.put_line(' pctincrease 0 );');

END;
/


NOTE 1: If the index is being used by another user, you will not be able to either validate or drop the index.
NOTE 2: Beware of the primary key constraint.
General details about the index can also be found by:
Analyze index compute statistics;
Select * from user_indexes
where index_name= ‘’;
To obtain further detail about an index:
Analyze index validate structure;
The command:
Validate index ;
Performs the same function.
This places detailed information about the index in the table INDEX_STATS. This table can only contain one row, describing only the one index. This SQL also verifies the integrity of each data block in the index and checks for block corruption.
For example, to get the size of an index:
validate index ;
select name "INDEX NAME", blocks * 8192 "BYTES ALLOCATED",
btree_space "BYTES USED",
(btree_space / (blocks * 8192))*100 "PERCENT USED"
from index_stats;
This assumes a block size of 8K (i.e. 8192 bytes). It shows the number of bytes allocated to the index and the number of bytes actually used.
Note that it does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. To check this:
Analyze table validate structure cascade;