Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1034
  • Last Modified:

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?
0
ajaybelde
Asked:
ajaybelde
3 Solutions
 
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
johnsoneSenior 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
 
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
 
johnsoneSenior 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 GeerlingsDatabase 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now