Optimizer directives and SPL routines
Directives operate differently for a query in an SPL routine because a SELECT statement in an SPL routine is not necessarily optimized immediately before the database server executes it.
The optimizer creates a query plan for a SELECT statement in an SPL routine when the database server creates the SPL routine or during the execution of the UPDATE STATISTICS statement that include the FOR FUNCTION, FOR PROCEDURE, or FOR ROUTINE keywords.
The optimizer reads and applies directives at the time that it
creates the query plan. Because it stores the query plan in a system
catalog table, the SELECT statement is not reoptimized when it is
executed. Therefore, settings of IFX_DIRECTIVES and DIRECTIVES
affect SELECT statements inside an SPL routine
when they are set at any of the following times:
- Before the CREATE PROCEDURE statement
- Before the UPDATE STATISTICS FOR ROUTINE statements that cause SQL data-manipulation statements in SPL routines to be optimized
- During certain circumstances when SELECT statements have variables supplied at runtime