> Hi Group,
>
> Is it safe to recompute an index through a PSQL 'execute statement'
> call? I wish to implement a stored procedure to update all the
> statistics. I plan to call it periodically (ie when the software is
> upgraded), or on demand if customers are experiencing slow selects to
> assist Firebird choosing a sensible PLAN.
>
> eg:
>
> CREATE OR ALTER PROCEDURE SP_CALCSELECTIVITY
> AS
> DECLARE VARIABLE IDX VARCHAR(50);
> DECLARE VARIABLE STMT VARCHAR(75);
> BEGIN
> FOR
> SELECT RDB$INDEX_NAME
> FROM RDB$INDICES
> INTO :IDX
> DO
> BEGIN
> STMT = 'SET STATISTICS INDEX ' || :IDX;
> EXECUTE STATEMENT :STMT;
> END
> END
> ^
>
> Thoughts? Comments? Warnings?
>
> Adam

I've always done it via separate calls to system tables then calls to the
indexes contained therein but I can't see a problem with your single call.
I have to confess, though, that I never had a user ring me up and say
"hey... what did you do? it's running so much faster now!!". Even *I*
couldn't tell the difference - ever.
I did, however find an index missing once (one I thought should always have
been present but wasn't) so I added it and even though users never
commented, I asked a few and they did sort of say "oh yeh - it is running a
bit faster in that browse now isn't it"..

so good luck!
Alan