phil_hhn 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...
>
>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?
>Thanks,
>Phil
>
>

Phil,

the ALTER INDEX statement could not be used on Fk/PK or other "system
(maintained) indices, you should drop/create the constraint.

BUT, you don't need this. you could just use

SET STATISTICS INDEX YOUR_INDEX_NAME

after commit all statiscts are up-to-date.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br