How to disable indexes while inserting data?

I am loading 300million records into a table. It has been taking long time to insert.
I want to disable Indexes on that table to make it faster. Can i disable them with out stopping the load process?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

johnsoneConnect With a Mentor Senior Oracle DBACommented:
You can make the index unusable with this command:


However, in order to make it usable again, it must be either rebuilt or dropped and recreated.
Have you ever considering drop index and create in the end.
slightwv (䄆 Netminder) Commented:
I don't believe you can 'disable' an index.  I think you will need to drop it.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

ajaybeldeAuthor Commented:
To drop indexes the load process should be terminated otherwise it will give "RESOURCE BUSY" error. Isn't it?
So, I just wanna make those indexes unusable.
ajaybeldeAuthor Commented:
I can drop and recreate after load is done.
I wanna make sure whether i can make them unusable on the fly or not?
johnsoneConnect With a Mentor Senior Oracle DBACommented:
I would drop them before the load, then recreate them after the load.

My guess is that making it unusable will require the same lock as dropping the index.
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
No, you cannot make an Oracle index unusable "on-the-fly" while a data load process is already running to add records to the table.  You need to drop the index(es) before the load, then re-create the index(es) after the load.
All Courses

From novice to tech pro — start learning today.