I have a question to piggy-back onto yours! Does SET STATISTICS work or
should we be setting the index inactive and active again. I have read
conflicting articles online and am not sure which one works best. We
have to dump in large quantities of data on a daily basis (175k per
table for 5-7 tables) and would like the stats to be up to date. After
setting the indexes inactive and active again by hand between each of
the large table steps (the tables are linked using foreign keys) we have
noticed a 15% decrease in time. But I am not sure of the difference
between doing that and running set statistics. Anyone know if one way
is better then another?

-steve

Nick Upson wrote:

>
> If I use the SP below to rebuild all index statistics which process
> actually does the work. I could see that it could be either the
> process that runs the SP (which is what I actually want) or it could
> be the next process to try and use an index (similiar to GC), or its
> probably something else I haven't thought of.
>
> ALTER PROCEDURE PR_SYS_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
>
>