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?
ajaybeldeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

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

ALTER INDEX <indx> UNUSABLE;


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

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.
0
 
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?
0
 
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.
0
 
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.
0
All Courses

From novice to tech pro — start learning today.