Database Optimizer (Optimizer)

Optimizer is the core of a DB engine!

Actual run plan (physical plan) = Access Method (access method) + Join algorithm + execution order

1.access method

1) Index Scan

2) file scan

2.join algorithm

1) Nested-loop

2) Hash

3) Sort-merge

3. Execution order

A complex query is nested by some simple query (single block),

Such as:
Select a from T1 where t1.b> (select t2.b from T2 where t2.b=1)

Can be decomposed into two simple query:

1) Select a from T1 where t1.b> ()

2) Select t2.b from T2 where t2.b=1

For each query, according to some equality formulas, such as

Produce different order of execution.

A query such as: SELECT * from A,b,c

How many kinds of plans are there:

Access Method:2^3=8

Join:3^2=9

Order:3!=6

Total: 8*9*6=432

In this way, a query has a different execution plan, how to choose the best?

Common methods:

1.costed-base

Calculate (estimate) The IO cost for each step in the plan, and the number of tuple outputs.

2. Rule-base

Based on some experience, query features are executed in a specific order. Example: If SQL1 is a type, use Plan1

3.random

Randomly select from many plan to estimate its cost.

(Oracle Database Rbo and CBO refer to rule-based optimization and cost-based optimization.)

Database Optimizer (Optimizer)