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.
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.
answered Nov 24 '09 at 09:14 AM
its true we all can enjoy experiencing your thought
answered Jul 20 '12 at 09:04 AM
To disable the index use alter index index-name unusable and alter index index-name rebuild to enable the index again
answered Nov 24 '09 at 03:43 AM