1. Introduction
  2. Query Optimization

5 Query Optimization

TimesTen has a cost-based query optimizer that ensures efficient data access by automatically determining the most efficient way to run a given query by considering possible query plans. Optimization is performed in the third stage of the compilation process.

The stages of compilation are shown in Figure 5-1.

Figure 5-1 Compilation Stages

Description of Figure 5-1 follows
Description of "Figure 5-1 Compilation Stages"

TimesTen invokes the optimizer for SQL statements when more than one execution plan is possible. The optimizer chooses what it thinks is the optimum plan. This plan persists until the statement is either invalidated or dropped by the application.

The optimizer determines the cost of a plan based on:

  • Table and column statistics

  • Metadata information (such as referential integrity, primary key)

  • The presence or absence of indexes. Index choices, including the creation of temporary indexes.

  • The volume of data

  • The number of unique values

  • The selectivity of predicates

  • Scan methods (full table scan, rowid lookup, range index scan, hash index lookup)

  • Join algorithm choice (nested loop join, nested loop join with indexes, or merge join)

This chapter includes the following topics:

See The TimesTen Query Optimizer in Oracle TimesTen In-Memory Database Operations Guide.