Cost estimation for queries

At run time, the optimizer chooses an optimal access method for the query by calculating an implementation cost based on the current state of the database. The optimizer models the access cost of each of the following:

• Reading rows directly from the table (table scan processing)
• Reading rows through an index (using either index scan-key selection or index scan-key positioning)
• Creating an index directly from the dataspace
• Creating an index from an existing index (index-from-index)
• Using the query sort routine or hashing method (if conditions are satisfied)
• Using bitmap processing

The cost of a particular method is the sum of:

• The start-up cost
• The cost associated with the given optimization mode.
  • Costs associated with optimization modes when using SQL: For SQL, the precompile option ALWCPYDTA and the OPTIMIZE FOR n ROWS clause indicate to the query optimizer the optimization goal to be achieved.
    • The optimizer can optimize SQL queries with one of two goals:
      1. Minimize the time required to retrieve the first buffer of rows from the table. This goal biases the optimization towards not creating an index.

         Either a data scan or an existing index is preferred. This mode can be specified in two ways:

         1. The OPTIMIZE FOR n ROWS allows the users to specify the number of rows they expect to retrieve from the query.

            The optimizer uses this value to determine the percentage of rows that will be returned and optimizes accordingly. A small value instructs the optimizer to minimize the time required to retrieve the first n rows.

         2. Specifying ALWCPYDTA(*NONE) or (*YES) as a precompiler parameter allows the optimizer to minimize the time that it requires to retrieve the first 3% of the resulting rows.

            This option is effective only if the OPTIMIZE FOR n ROWS was not specified.

      2. Minimize the time to process the whole query assuming that all selected rows are returned to the application. This option does not bias the optimizer to any particular access method. This mode can be specified in two ways:
         1. The OPTIMIZE FOR n ROWS allows the users to specify the number of rows they expect to retrieve from the query.

            The optimizer uses this value to determine the percentage of rows that will be returned and optimizes accordingly. A value greater than or equal to the expected number of resulting rows instructs the optimizer to minimize the time required to run the entire query.

         2. ALWCPYDTA(*OPTIMIZE) specified as a precompiler parameter.

            This option is effective only if the OPTIMIZE FOR n ROWS is not specified.

  • Costs associated with optimization modes when using OPNQRYF:
    • The cost associated with the given optimization parameter (*FIRSTIO, *ALLIO, or *MINWAIT).
      • *FIRSTIO -- Minimize the time required to retrieve the first buffer of rows from the table. Biases the optimization toward not creating an index. Either a data scan or an existing index is preferred. When *FIRSTIO is selected, users may also pass in the number of rows they expect to retrieve from the query. The optimizer uses this value to determine the percentage of rows that will be returned and optimizes accordingly. A small value would minimize the time required to retrieve the first n rows, similar to *FIRSTIO. A large value would minimize the time to retrieve all n rows, similar to *ALLIO.
      • *ALLIO -- Minimize the time to process the whole query assuming that all query rows are read from the table. This option does not bias the optimizer to any particular access method.

        Note:

        If you specify ALWCPYDTA(*OPTIMIZE) and the query optimizer decides to use the sort routine, your query resolves according to the *ALLIO optimize parameter.

      • *MINWAIT-Minimize delays when reading rows from the table. Minimize I/O time at the expense of open time. This option biases optimization toward either creating a temporary index or performing a sort. Either an index is created or an existing index is used.
• The cost of any index creations
• The cost of the expected number of page faults to read the rows and the cost of processing the expected number of rows.

  Page faults and number of rows processed may be predicted by statistics the optimizer can obtain from the database objects, including:

  • Table size
  • Row size
  • Index size
  • Key size

  Page faults can also be greatly affected if index only access can be performed, thus eliminating any random input and output to the data space.

  A weighted measure of the expected number of rows to process is based on what the relational operators in the row selection predicates, default filter factors, are likely to retrieve:

  • 10% for equal
  • 33% for less-than, greater-than, less-than-equal-to, or greater-than-equal-to
  • 90% for not equal
  • 25% for BETWEEN range (OPNQRYF %RANGE)
  • 10% for each IN list value (OPNQRYF %VALUES)

  Key range estimate is a method the optimizer uses to gain more accurate estimates of the number of expected rows selected from one or more selection predicates. The optimizer estimates by applying the selection predicates against the left-most keys of an existing index. The default filter factors can then be further refined by the estimate based on the key range. If an index exists whose left-most keys match columns used in row selection predicates, that index can be used to estimate the number of keys that match the selection criteria. The estimate of the number of keys is based on the number of pages and key density of the machine index and is done without actually accessing the keys. Full indexes over columns used in selection predicates can significantly help optimization.

  Page faults and the number of rows processed are dependent on the type of access the optimizer chooses. Refer to Data access on DB2 UDB for AS/400: data access paths and methods for more information on access methods.