Home » RDBMS Server » Performance Tuning » Should an index be rebuilt or recreated during data load.
Should an index be rebuilt or recreated during data load. [message #445494] Tue, 02 March 2010 03:44 Go to next message
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
Michel Cadot
Messages: 63810
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
Previous Topic: TEMP TABLESPACE
Next Topic: Can this query be improved for performance?
Goto Forum:
  


Current Time: Thu Sep 29 15:58:52 CDT 2016

Total time taken to generate the page: 0.12954 seconds