The query plan produced by the optimizer is determined by optimizer algorithms and by:

  • The SQL statement itself, especially its predicates.
  • Information from the schema, such as the definition of tables used by the statement. Index definitions are especially important.
  • Information from the statistics tables about the cardinalities of the tables used, about data frequency within indexes, and about data distribution within columns.

The SQL statement itself is of utmost importance. The predicates from the WHERE clause form the basis for estimating how much data will be read for each table, and how much data each join will produce. Supplying as many precise and accurate predicates as possible is vital to good optimization.