Derby's cost-based optimization

The query optimizer makes a number of cost-based decisions.

These decisions determine:

• Which index (if any) to use on each table in a query (see About the optimizer's choice of access path)
• The join order (see About the optimizer's choice of join order)
• The join strategy (see About the optimizer's choice of join strategy)
• Whether to avoid additional sorting (see About the optimizer's choice of sort avoidance)
• Automatic lock escalation (see About the system's selection of lock granularity)
• Whether to use bulk fetch (see About the optimizer's selection of bulk fetch)

• About the optimizer's choice of access path
  The optimizer's choice of access path can depend on the number of rows it will have to read. It tries to choose a path that requires the fewest number of rows read.
• About the optimizer's choice of join order
  The optimizer chooses the optimal join order as well as the optimal index for each table.
• About the optimizer's choice of join strategy
  The optimizer compares the cost of choosing a hash join (if a hash join is possible) to the cost of choosing a nested loop join and chooses the cheaper strategy.
• About the optimizer's choice of sort avoidance
  Some SQL statements require that data be ordered, including those with ORDER BY, GROUP BY, and DISTINCT. MIN() and MAX() aggregates also require ordering of data.
• About the system's selection of lock granularity
  When a system is configured for row-level locking, it decides whether to use table-level locking or row-level locking for each table in each DML statement.
• About the optimizer's selection of bulk fetch
  When Derby retrieves data from a conglomerate, it can fetch more than one row at a time. Fetching more than one row at a time is called bulk fetch.