Should an index be rebuilt or recreated during data load. [message #445494] |
Tue, 02 March 2010 03:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ziggy25
Messages: 206 Registered: July 2005
|
Senior Member |
|
|
I have a sql loader script that loads about 2.5 million rows on to a table. I had a few problems with the peformance of the table so i decided to create an index on two of the most commonly used columns on the table.
Ever since i created the index, i noticed that the SQL Loader has slowed down drastically. Do you think it would make any difference if i drop the index, load the data and recreate it? Or is there a way to disable the index during the data load?
The other thing is when i created the index, Oracle was not using it. It was always going for the full table scan option. Only when i gathered statistics did it decide to use the index. Why is this the case, especially given that the table is truncated during every data load.
Thanks
|
|
|
Re: Should an index be rebuilt or recreated during data load. [message #445499 is a reply to message #445494] |
Tue, 02 March 2010 04:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Ever since i created the index, i noticed that the SQL Loader has slowed down drastically.
Nothing about this as Oracle has to maintain indexes in addition to the table (above all if you do not use the DIRECT mode).
Quote:Do you think it would make any difference if i drop the index, load the data and recreate it?
Yes it will be faster.
Quote:Or is there a way to disable the index during the data load?
Use DIRECT mode.
Quote:The other thing is when i created the index, Oracle was not using it. It was always going for the full table scan option. Only when i gathered statistics did it decide to use the index. Why is this the case, especially given that the table is truncated during every data load.
Simply because statistics are not to date and lead Oracle to wrong conclusions.
Regards
Michel
|
|
|