Loading of data - do I drop the index?

[ASmee]

I am loading a huge amount of data into three tables and currently the scripts drop the indexes on these tables (one is unique), loads the data and then recreates the indexes.

Would it be more effective to load the data and then rebuild the indexes?

 

[alexhu]

That's what you're doing aren't you ?

Alex

 

[ASmee]

No I want to insert the data and then use the alter database rebuild index command rather than the highly intensive create index after dropping them.

 

[carp]

OK, then
1. Load your data.
2. Rebuild your indexes.

However, depending on how much your indexes change, you might be better off dropping/recreating the indexes.

Bear in mind that (1) maintaining indexes is going to have an impact on your performance during data loads and (2) rebuilding indexes can impose a fair amount of storage during the rebuild.

 

[alexhu]

I agree with carp, whichever way you do it will be a performance hit.

How big is this 'huge' amount of data anyway ?

Alex

 

[sem]

But in this case index would be changed with each new record. As a matter of fact batch operations are more efficient than the same changes applied to each record, thus creating a new index is far less expensive.

Regards, Dima

http://seminihin.narod.ru

 

[THoey]

I agree with SEM especially if you have a couple of indexes on the table. Drop the indexes, perform the load, and then re-create the indexes. I have two instances that use SQL*Loader to load any where from 300K to 700K records each night each. This is the way that I go.

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[avjoshi]

I would say, it depends. I can think of two different scenarios of data load.
1. If you loading a huge amount of data in a huge table then you may want to rebuild the indexes after the load is complete.
2. If you are doing complete refresh of the tables then you are better off dropping the indexes before load and recreating them afterwards.

If scenario one applies to you then I would suggest that you may want to consider partitioning. That way you have an option of rebuilding just the partition index.


Anand

 

[BJCooperIT]

Bear in mind that if you drop a unique index, then you face the possibility of loading duplicate data.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[avjoshi]

Another point. If the data is loaded using SQL loader using direct mode then the it ignores the unique constraint on the table rendering index invalid.

Anand