Difference Between Drop & Disabling the Index

Database

Difference Between Drop & Disabling the Index

Hi Gurus,
Can any one help me on finding the difference between - dropping the index and disabling the index?
1. What will happen if just drop the index while records are loading to the target, once loading completing i can recreate the index?
2. What will happen if just disable the index while records are loading to the target, once loading completes, can i able to enable the index again?

Please help me to find the difference in this mechanism.
Thanks~!
2 REPLIES
N/A

Re: Difference Between Drop & Disabling the Index

I am not sure we have the facility to disable indexes.

Drop and recreate Index works fine. We use this strategy when we insert data into existing tables which has huge volume of data. Loading with this strategy is much faster than with indexes.
WAQ
N/A

Re: Difference Between Drop & Disabling the Index

I am not sure which index are you talking about.
> If you are tallking about PRIMARY INDEX then you can not drop it, if data is loaded. Primary index is created at the time of table creation and it will remain same for the entire life of the table. In order to change or drop that index, you need to re-create (OR make the table empty) the table.
> If you are talking about SECONDARY INDEX then yes you can drop it any time and recreate it (even if the table is populated). I think thats the index which Toad mentioned in his post.