Home > tips > Moving Data and Creating Local Index In Huge, Partitioned Tables

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: