Cost Determination

The SQL Optimizer determines the costs of every possible search strategy.

To determine the costs of a search strategy, the SQL Optimizer estimates how many I/O operations the database system would likely need to execute for that search strategy. To make this estimate, the SQL Optimizer takes random samples and uses statistical information supplied by the database system (see SQL Optimizer Statistics).

The SQL Optimizer does not consider costs incurred by resources such as CPU time or memory usage.

The cost of a search strategy is largely dependent upon the form of the search condition specified in the SQL statement. Formulate SQL statements in such a way that the database system only has to search the table rows that are actually needed, and the result and intermediate result tables can be kept as small as possible.

You can use the EXPLAIN statement to display the search strategy and costs determined by the SQL Optimizer for an SQL statement.

See also:

SQL Optimizer Procedure