Maya Opperman wrote:

>
> OK I've been reading up on online FAQ's and Helen's brilliant book, but
> I just want to double check that the following is safe to do, and won't
> result in users being able to post duplicate keys, if something goes
> wrong with reactivating an index for some odd reason:

First, the procedure below doesn't deactivate the indexes at all.
That's probably a typo. Second, there's no reason I can think of
to deactivate and reactivate all indexes on a regular basis. If
you're having problems with indexes, tell us what's going on so the
problem can be fixed. Third, Firebird indexes are designed to be
used for months or years without ever needing a rebuild. Finally,
resetting the statistics from time to time is a good idea, but
that's very low overhead compared with rebuilding a large index.

Between the time you deactivate a unique index and the time you
rebuild it, someone could insert a duplicate record. Then the
rebuild would fail. If the rebuild fails, then others can add
more duplicates. If you need to rebuild indexes that control
uniqueness, take the database off-line.

Cheers,

Ann

>
> (For Firebird 1.5)
>
> SET TERM !! ;
>
> EXECUTE BLOCK AS
> DECLARE VARIABLE stmt VARCHAR(1000);
> BEGIN
> for select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;'
> from rdb$indices
> into :stmt
> do EXECUTE STATEMENT :stmt;
>
> for select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;'
> from rdb$indices
> into :stmt
> do EXECUTE STATEMENT :stmt;
> END!!
>
> SET TERM ; !!
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>