> > >> 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
> > [snip]
> > I thought that the plans were chosen when the procedure was first
> > invoked and retained by the server until the database is closed.
> > And I don't know of a way to force a recompilation except to alter the
> > procedure to itself.
>
> With a view towards the future...
>
> If the statistics for an index were to be recalculated, then it would
> seem appropriate to 'mark' the SPs which reference the table as 'dirty'
> to have the system recalculate the plan at the next appropriate
> invocation.

Sounds fairly easy.

But anyway, to avoid this if it's not needed -- can someone look
into the source and check the current behaviour?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com