|
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.
(comments are locked)
|
|
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. Can't you enable/disable an index constraint? Or does this drop & recreate the index behind the scenes?
Nov 24 '09 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 '09 at 03:51 PM
Andrew Mobbs
(comments are locked)
|
|
its true we all can enjoy experiencing your thought
(comments are locked)
|
|
To disable the index use alter index index-name unusable and alter index index-name rebuild to enable the index again
(comments are locked)
|
