Re: When to drop indexes

In article <31add630.5277413_at_news2.ios.com>,

        chuckh_at_dvol.com (Chuck Hamilton) writes:
>When deleting or inserting a lot of rows for a table I've been told
>it's usually better to drop all it's indexes, make your changes, then
>rebuild the indexes. At what point does this become effective? Is
>there a rule of thumb for determining when it'll take more time to
>rebuild than to just leave them alone?
>
>For example, suppose I have a snapshot that has approx 1 millions rows
>in it. There are only two indexes on the snapshot - the snapshot index
>proper, and a user created index. The snapshot log says there's 54,000
>rows to be inserted. Should I drop the user index and rebuild it
>afterwards? Or should I leave it alone?

I won't address your specific issue of a partial population, but in several cases, I have tables needing complete repopulation using SQL load. These tables are in the 6,000,000 row size. By testing, I have determined that "drop index, truncate table, sqlload, create index unrecoverable" is up to 50% faster than "truncate table, sqlload". I don't know what quantitative impact the unrecoverable option only has.

Anyways, hope this info gives you something else to try and benchmark.

Bill Received on Sat Jun 01 1996 - 00:00:00 CEST