Access plan validation
An access plan is a control structure that describes the actions necessary to satisfy each query request. It contains information about the data and how to extract it. For any query, whenever optimization occurs, the query optimizer develops an optimized plan of how to access the requested data.
To improve performance, an access plan is saved (see exceptions below) once it is built so as to be available for potentially future runs of the query. However, the optimizer has dynamic replan capability. This means that even if previously built (and saved) plan is found, the optimizer may rebuild it if it determines that a more optimal plan is possible. This allows for maximum flexibility while still taking advantage of saved plans.
- For dynamic SQL, an access plan is created at prepare or open time. However, optimization uses the host variable values to determine an optimal plan. Therefore, a plan built at prepare time may be rebuilt the first time the query is opened (when the host variable values are present).
- For an iSeries™ program that contains static embedded SQL, an access plan is initially created at compile time. Again, since optimization uses the host variable values to determine an optimal plan, the compile time plan may be rebuilt the first time the query is opened.
- For Open Query File (OPNQRYF), an access plan is created but is not saved. A new access plan is created each time the OPNQRYF command is processed.
- For Query/400, an access plan is saved as part of the query definition object.
In all cases above where a plan is saved above, including static SQL, dynamic replan can still apply as the queries are run over time.
The access plan is validated when the query is opened. Validation includes the following:
- Verifying that the same tables are referenced in the query as in the access plan. For example, the tables were not deleted and recreated or that the tables resolved by using *LIBL have not changed.
- Verifying that the indexes used to implement the query, still exist.
- Verifying that the table size or predicate selectivity has not changed significantly.
- Verifying that QAQQINI options have not changed.
Parent topic:
Processing queries: Overview