OraFAQ Forum: Performance Tuning » Should an index be rebuilt or recreated during data load.

Home » RDBMS Server » Performance Tuning » Should an index be rebuilt or recreated during data load.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Should an index be rebuilt or recreated during data load. [message #445494]

Tue, 02 March 2010 03:44

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

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	TEMP TABLESPACE
Next Topic:	Can this query be improved for performance?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 14:14:12 CST 2024