Optimization Time and Memory Usage

The optimizer is designed to generate the best possible plan within reasonable time and memory constraints.

Because of the size of the search space (which grows exponentially with the number of tables in a join), the optimizer cannot always choose an optimal plan for every query. Instead, the goal of the optimizer is to choose a good plan from among a set of plans generated by using strategies for finding the most promising areas within the search-space of plans. Since query optimization is relatively expensive relative to execution, the optimizer is designed to give precedence to runtime over optimization time.

The plans generated by the optimizer emphasize performance over memory usage. The optimizer may designate the use of significant amounts of temporary memory space in order to speed up runtime. In memory-constrained environments, applications can use the optimizer hints described in Optimizer Hints to disable the use of temporary indexes and tables in order to create plans that trade maximum performance for reduced memory usage.