Database Optimizer

Optimizer #

An optimizer is the core engine within a DBMS that generates the optimal processing path to execute SQL in the fastest and most efficient way.

When a user requests a result set using Structured Query Language (SQL), the processing path required to generate it is automatically created by the optimizer built into the DBMS.

The SQL processing path generated by the optimizer is called an Execution Plan.

SQL Optimization Process of the Optimizer #

1. To execute the query submitted by the user, the optimizer finds candidate execution plans.
2. It calculates the estimated cost of each execution plan using object statistics and system statistics collected in advance in the data dictionary.
3. It compares each execution plan and selects the one with the lowest cost.

There are two types of optimizers: Rule-Based Optimizer and Cost-Based Optimizer.

Rule-Based Optimizer (RBO) #

A Rule-Based Optimizer is an optimizer that establishes rules based on execution speed, adopting the method with higher priority.

Here, a rule refers to the priority by access path, where index structure, operations, and condition clause forms are the main factors determining priority. The priorities of the rules are as follows:

1. Single row using ROWID
2. Single row by cluster join
3. Single row by hash cluster key with a unique primary key
4. Single row by unique primary key
5. Cluster join
6. Hash cluster join
7. Index cluster key
8. Composite column index
9. Single column index
10. Searching a limited range on an indexed column
11. Searching an unlimited range on an indexed column
12. Sort-merge join
13. Finding MAX, MIN on an indexed column
14. Executing ORDER BY on an indexed column
15. Full table scan

Cost-Based Optimizer (CBO) #

A Cost-Based Optimizer, as its name suggests, performs optimization based on cost.

Here, cost refers to the amount of work or time required to execute a query. A CBO can generate up to 2,000 execution plans and then executes the one with the minimum cost. The cost used to establish the execution plan at this time is an estimate. Therefore, to predict costs, CBO uses various object statistics (for tables, indexes, columns, etc.) and system statistics (CPU speed, disk I/O speed, etc.).

While Oracle, with its long history, started with RBO, other commercial RDBMS adopted CBO from their inception. Oracle also discontinued support for RBO and adopted CBO starting with version 10g.