What are SQL optimizers RBO and CBO?

RBO (Rule-Based Optimizer) and CBO (Cost-Based Optimizer) are two different types of SQL optimizers.

RBO is a rule-based optimizer that uses a set of predefined rules and heuristic algorithms to choose the best query execution plan. It relies primarily on the lack of statistical information during the optimization process and cannot accurately estimate the cost of a query. The advantage of RBO is its simplicity and stable execution plans, while its disadvantage is lower performance and difficulty adapting to complex queries.

The Cost-Based Optimizer (CBO) is a optimizer that evaluates different query execution plans based on statistical information and cost models, selecting the plan with the lowest cost as the optimal execution strategy. CBO can estimate the cost of queries more accurately and takes into account factors such as index selection, join order, and join methods when selecting an execution plan. The advantages of CBO are its ability to adapt to different queries and data distributions, while its disadvantages are its complexity and potential performance issues.

In modern database systems, most use CBO as the default optimizer because it can make better optimization decisions based on actual circumstances. However, in certain special cases, such as older versions of database systems or specific query scenarios, RBO may be used.