Background documentationCost Determination Locate this document in the navigation structure

 

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: SQL Optimizer Statistics.

Note Note

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

End of the note.

The cost of a search strategy is largely dependent upon the form of the search condition specified in the SQL statement.

Recommendation Recommendation

  • Formulate SQL statements in such a way that the database system only needs to search in the tables for the rows that are actually required.

  • Formulate SQL statements in such a way that the database system can keep the results and intermediate result tables as small as possible.

End of the recommendation.

You can use the EXPLAIN statement to display which search strategy and costs the SQL Optimizer has selected for an SQL statement.

More Information