How do we insert data about 2 million rows into a oracle database table where we have many indexes on it? I know that one option is disabling index and then inserting the data. Can anyone tell me what r the other options?
A:
Not sure how you are inserting the records; if you can; insert the data in smaller chunks. In my experience 50 sets of 20k records is often quicker than 1 x 1000000
Make sure your database files are large enough before you start save you from database growth during the insert ...
u07ch
2009-04-27 19:24:11
A:
If you are sure about the data, besides the index you can disable referential and constraint checks. You can also lower the transaction isolation level.
All these options come with a price, though. Each option increases your risk of having corrupt data in the sense that you may end up with null FK's etc.
Miguel Ping
2009-04-27 19:24:43
A:
Check SQL*Loader out (especially the paragraph about performance optimization) : it is the standard bulk loading utility for Oracle, and it does a good job once you know how to use it (as always with Oracle).
Mac
2009-04-28 14:00:21