Re: When to drop indexes

Chuck,

IN GENERAL, since you have 1 million rows, I expect it is faster to insert 54,000 rows with the index on. I suggest you actually do some timing experiments to find out for your database with the given init parameters and block size. Create a copy of the table, make sure TIMED_STATISTICS = TRUE in your init file, and do the insert. This is best done in a test database if you have one (assuming all parameters are identical). Then do another test timing the drop index, insert, and create index. Note that if you have any foreign keys on these indexed columns you have to handle them as well. If you have any questions, you can reach me at 804-262-6332.

Steve

In <4omuca$4m9_at_nntpa.cb.att.com> Naren Chintala <naren_at_mink.att.com> writes:
>
>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