Solved

How to disable indexes while inserting data?

Posted on 2011-09-30
7
649 Views
Last Modified: 2012-05-12
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
Comment
Question by:ajaybelde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:MrNetic
ID: 36891802
Have you ever considering drop index and create in the end.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36891820
I don't believe you can 'disable' an index.  I think you will need to drop it.
0
 

Author Comment

by:ajaybelde
ID: 36891826
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 35

Accepted Solution

by:
johnsone earned 333 total points
ID: 36891927
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
 

Author Comment

by:ajaybelde
ID: 36892081
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 333 total points
ID: 36892208
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 167 total points
ID: 36905888
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

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
pivot rows to columns 1 60
Checking for column width 8 49
Huge BLOB data type in Oracle how to retrieve in vb.net and crystal reports 6 78
replicate in oracle 13 45
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question