When a stored procedure or trigger is compiled, are the plans for its
queries chosen at that time? If so, they should be recompiled after
recomputing index statistics.
In that case, does anyone know how to recompile all stored procedures
and triggers, in a similar fashion to the procedure below?

Thanks, Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ian A. Newby
Sent: Wednesday, June 01, 2005 4:31 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Controlling expansion of database file /
grow-by option?

Hi David,

> I'm not familiar with updating index stats. Is that feature available
> through ODBC... or do I need to use the services API?

As you're using 1.5 you can write a stored procedure to do it...

CREATE PROCEDURE SYS_RECALC_SELECTIVITY
AS
DECLARE VARIABLE INDEXNAME CHAR(31);
begin
for select rdb$index_name from rdb$indices into :indexname do
begin
execute statement 'set statistics index ' || :indexname;
end
end

This is the one I do. Run it occasionally as you see fit.

Regards
Ian Newby





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net 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