Re: When to drop indexes

Naren Chintala <naren_at_mink.att.com> wrote:

>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
>

After further investigation I found that the snapshots were not doing a fast refresh at all. They were doing complete refreshes. That's why it was taking so long. Apparently the snapshot logs were created _after_ the snapshot and according to the manual that will force the first refresh to be a complete refresh. Oddly though, it wouldn't let us to anything but a complete refresh no matter how many times we refreshed! I ended up dropping and recreating both the logs and the snapshots. That seems to have fixed the problem.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself