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 once it is built (see following exceptions), to be available for potentially future runs of the query. However, the optimizer has dynamic replan capability. This means that even if a previously built (and saved) plan is found, the optimizer could rebuild it if a more optimal plan is possible. This process 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 could be rebuilt the first time the query is opened (when the host variable values are present).
  • For an IBM® i 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 could 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 the preceding cases where a plan is saved, 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.