At 12:40 PM 18/12/2005 +0000, you wrote:

>Hi, I have a connection open to a Firebird (1.5.2) database in
>auto-commit mode. I added several records to an n-to-n table, and then
>ran some queries and they are very slow (takes minutes). I noticed in
>IBExpert that the 'stats' for all the Indexes are 0, so got IBExpert
>to rebuild the index for one of the columns I use. The query is now
>almost instant.
>The index I rebuilt is on a FK which points to the PK of another table.
>
>So my questions:
>
>1) Why hasn't this index already been computed? I thought it was
>automatic...

It is - but it only happens a) at startup (when the first user connects to
the database after all have disconnected).

>2) How can I (programatically) recompute the index (without gbak)? I
>looked up "The FB Book" and tried:
>ALTER INDEX FK_COPYVALU_REF_74836_COPIES INACTIVE
>ALTER INDEX FK_COPYVALU_REF_74836_COPIES ACTIVE
>
>However this blew up on the first statement with the message:
>MODIFY RDB$INDICESS failed
>action cancelled by trigger (3) to preserve data integrity
>Cannot deactivate primary index
>
>So is there another way?

You can't drop constraint indexes - you need to drop the constraint and
then re-apply it.

If that was a foreign key index, it's giving you the wrong message,
too. It should be telling you "can't deactivate foreign key
index". Grrrrr. One we missed for Fb 1.5.3.

./heLen