Re: When to drop indexes

chuckh_at_dvol.com (Chuck Hamilton) wrote:
>To drop indexes or not to drop indexes, that is the question.
>
>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?
>
>TIA
>--
>Chuck Hamilton
>chuckh_at_dvol.com
>

We have exactly the same situation (index created by the snapshot and the user created snapshot). We refresh(fast) about 150,000 records (once a day). We have not faced any performance problems due to the user created index. What we do is perform a query using the user index after the snapshot refresh is complete. If the query performance is not as per your requirements, we recreate the user index.

Naren Chintala
AT&T
naren_at_mink.att.com Received on Fri May 31 1996 - 00:00:00 CEST