Re: Stored Procedures and querries.

From "Incorporating Stored Procedures and Triggers into Your Informix Databases Training Manual" Version 01-95 Page 6-128: When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist or may not be available), the create procedure will not fail. The SQL in this case will be optimized the first time the stored procedure is executed, and the query plan will be stored for use by other processes. An SQL statement will also be optimized at execution time if any DDL statement (e.g. alter table, drop index, create index) has been run that might alter the query plan. Altering a table which is linked to another table via a referential constraint (in either direction) will cause re-optimization of procedures which operate on the referenced table. The dependency list is used to track which changes would cause re-optimization. If "update statistics for table" is run for any table involved in the query, the SQL statement will be re-optimized the next time the stored procedure is executed. sane man wrote: > > Hi, > I was under the impression that when we put a > sql statement in a SP. the sql's query plan is > decided when we create the procedure. This seems > not to be the case. Can some explain to me when > in SP does the query plan for the Query is > decided in SP. Hopefully not when he query is > executed. > > Thanks In Advance. > > Ramesh > > Reply to rramesh@csci.csc.com