Home » SQL & PL/SQL » SQL & PL/SQL » difference between dropping and unusable index (10g)
difference between dropping and unusable index [message #394524] Fri, 27 March 2009 11:02 Go to next message
reachmexyz
Messages: 8
Registered: February 2009
Location: New Jersey
Junior Member
Hello all.

In my case i am loading aroung 8 million records every month.
There are around 12 bitmap indexes on the table.
So before loading i am dropping the index and after the load is complete, recreating the index.
But as the data keeps increaing, recreating the dropped bitmap indexes will be a overhead.
Am i right about the above statement?
Instead of dropping and recreating the bitmap indexes, can i make use of making index 'unusable' before load and then 'rebuild' index after loading.
What is the difference between these two scenarios?
Also if the table is partitioned, can i drop the index for a single partition and recreate the index for only that partition.

Thanks in Advance
Re: difference between dropping and unusable index [message #394528 is a reply to message #394524] Fri, 27 March 2009 12:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Either way, dropping/recreating or rebuilding will almost certainly always take longer than just leaving the indexes in place during the import.
Re: difference between dropping and unusable index [message #394536 is a reply to message #394528] Fri, 27 March 2009 12:57 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ThomasG wrote on Fri, 27 March 2009 13:28
Either way, dropping/recreating or rebuilding will almost certainly always take longer than just leaving the indexes in place during the import.


It is my experience that the dropping of the index beforehand and then recreating after the load is faster. That is I think why an import will create indexes after the table is loaded with data.
Previous Topic: selecting 12 char
Next Topic: Query in 9i & 10g
Goto Forum:
  


Current Time: Fri Dec 09 05:38:01 CST 2016

Total time taken to generate the page: 0.07387 seconds