question

BI DWH BALA avatar image
BI DWH BALA asked

How to enable and disable indexes in oracle?

Hi,

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.

index
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Andrew Mobbs avatar image
Andrew Mobbs answered

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.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thecoop avatar image thecoop commented ·
Can't you enable/disable an index constraint? Or does this drop & recreate the index behind the scenes?
0 Likes 0 ·
Andrew Mobbs avatar image Andrew Mobbs commented ·
@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.
0 Likes 0 ·
user-74 avatar image
user-74 answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

devendra avatar image
devendra answered
its true we all can enjoy experiencing your thought
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.