Moving Data and Creating Local Index In Huge, Partitioned Tables

In order to get performance while creating a table and moving data with bulk inserts, it is a common way, first creating the table without index. Performance is gained with the help of not dealing with index manipulation for each row inserted, which means “single insert” operation.

You need to create the index after moving the data with insert /*+APPEND*/clause.

"Creating index" is the heart of the matter here... If you go with traditional approach like in small tables it will not work because of the reasons below:

-          new DML operations must wait for the index creation, which could cause space problems, application problems

-          getting ORA-01555 snaphot too old messages while creating the index

So what will I do in order not to bump into above conditions?

Answer: Apply divide and conquer approach, partitions is for this purpose... First create the index as unusable, and then issue rebuild clause to each partition. This could be done as following:

- Make the index unusable:

ALTER INDEX myindex UNUSABLE;

- Rebuild the index in each partition:

ALTER INDEX myindex REBUILD PARTITION mypartition;

- Check the status of index for each partition from:

SELECT PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
WHERE INDEX_NAME = 'MYINDEX';

Also note that if the index is associated with a constraint such as; primary/unique key constraint, you should better disable or drop the constraint, as for not getting error for DML operations take place after index is unusable.

You could get detailed information with regard to unusable state of indexes and index altering operations in the below links respectively:

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/indexes002.htm#CIHJIDJG

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/indexes004.htm#CIHJCEAJ