Pavel Cisar wrote:

> It's normal :-) You don't need to drop and recreate, just use ALTER INDEX
> INACTIVE and then ALTER INDEX ACTIVE for the same effect.
>
> Why the performance is better after that:
>
> 1) Index statistic is recomputed. It's computed only on index creation
> (not very usable for empty database when you create the schema :-),
> recreation by ALTER INDEX ACTIVE and by SET STATISTICS command. Index
> statistic is important for optimizer and updated value could result in
> different (not always better, but mostly far better) query execution
> plan.
>
> 2) The whole index B-tree is recreated as balanced. A lot of inserts,
> updates and deletes render the index B-tree structure less effective.
>
> Golden rule to keep your index healthy: You should recreate the index B-
> tree every time when about 30% of index keys was changed.

Thanks for the reply. I expect our users will be happier every 30% changed.

I hope this will become unnecessary in 2.0

Regards,

Mark