Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to disable indexes while inserting data?

Posted on 2011-09-30
7
Medium Priority
?
791 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 35

Accepted Solution

by:
johnsone earned 1332 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 1332 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 668 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

705 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