How to enable and disable indexes in oracle?


I am using Oracle 10G.

I am trying to enable or disable indexes on a table.

My scenario is as follows:

I am inserting millions of records in target tables. To increase performace, before inserting data into the tables, I would like to disable indexes. AFter the process is completed and I would like to re enable the indexes.

Is this a good pracitce? Am I going about it in the right way?

Any suggestions are welcome.

more ▼

asked Nov 23, 2009 at 11:42 PM in Default

avatar image

606 54 62 66

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Yes, if you have a large data load in a window where the table will not be queried, it may well be faster to drop the indexes and recreate them after the load.

The point where it becomes beneficial will depend on the data involved, the size of the table, and the size of the data load and the hardware. You would need to run tests to see if it applies in your case.

more ▼

answered Nov 24, 2009 at 09:14 AM

avatar image

Andrew Mobbs
1.6k 3 5

Can't you enable/disable an index constraint? Or does this drop & recreate the index behind the scenes?

Nov 24, 2009 at 12:18 PM thecoop

@thecoop - I'm confused by what you mean by "index constraint" - only some constraints (unique and PK) use indexes and not all indexes support constraints.

If you disable a Unique constraint what then happens depends on how the supporting index was created. If it was implicitly created by the ALTER TABLE ... ADD CONSTRAINT then it will be dropped. If it was explicitly created before the constraint then it will not be dropped.

Indexes can be marked unusable, which doesn't drop, but requires a full rebuild to "re-enable". In 11g they can also be marked Invisible, which hides them from the CBO.

Nov 25, 2009 at 03:51 PM Andrew Mobbs
(comments are locked)
10|1200 characters needed characters left

To disable the index use alter index index-name unusable and alter index index-name rebuild to enable the index again

more ▼

answered Nov 24, 2009 at 03:43 AM

avatar image

146 2 1

(comments are locked)
10|1200 characters needed characters left

its true we all can enjoy experiencing your thought

more ▼

answered Jul 20, 2012 at 09:04 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 23, 2009 at 11:42 PM

Seen: 26792 times

Last Updated: Jul 20, 2012 at 09:13 AM

Copyright 2018 Redgate Software. Privacy Policy