Before I shared the “Understanding SQL Execution Order”, Today I simply explain what is the Rule Based Optimization, Cost Based Optimization and the Query Execution Plan.
Understanding SQL Execution Order :
https://www.linkedin.com/pulse/understanding-sql-execution-order-buddika-abeykoon-momdc/
When we write an SQL query, we usually focus on what result we want. But have you ever wondered how a database decides the best way to retrieve that data?
SELECT * FROM employees WHERE department = 'IT';
If the employees table contains millions of rows, should the database scan the entire table or use an index?
There are two important concepts to understand,
— -Rule Based Optimization — RBO
— -Cost Based Optimization — CBO
- Rule Based Optimization — RBO
Rule Based Optimization uses predefined rules to choose an execution plan. Instead of estimating the cost of different execution plans using data statistics, a rule based optimizer follows established priorities.
Example : a traditional rule based optimizer might prefer using an available index over performing a full table scan.
- Cost Based Optimization — CBO
This task is bit different tahn before one, it evaluates possible execution plans and estimates their costs using information such as,
Table sizes
Index availability
Data distribution and statistics
Become a Medium member
Estimated rows to be processed
CPU and disk I/O requirements
Join methods and join order
The optimizer then chooses the plan it estimates will be the least expensive.
FROM employees WHERE department = 'IT';
Press enter or click to view image in full size
- RBO vs CBO
- How Can We See the Execution Plan?
We can use ‘EXPLAIN’ to inspect the plan selected by the optimizer.
EXPLAIN SELECT * FROM employees WHERE department = 'IT';
‘EXPLAIN’ can not run the sql query. So, we can examine the access method, possible indexes, selected index and estimated number of rows.
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'IT';
***This executes the query and reports actual execution information, helping us compare estimates with real performance.
- Which Approach Do Modern Databases Use?
Many modern relational databases use Cost Based Optimization. MySQL, PostgreSQL, and Oracle use cost based optimization techniques. Oracle also has a historical rule based optimizer, which is different from its modern cost based approach.
However, we cannot gurantee the cost based optimizer is fastest possible execution every time. Its decisions depend on the quality of its statistics, cost estimates and available execution strategies.
Finally, understanding SQL syntax is essential but it’s equally important to know how a database determines its execution plan. The Rule Based Optimizer uses fixed rules while the Cost Based Optimizer assesses estimated costs. When a query runs slowly, it’s crucial not to rush into adding an index or rewriting the SQL. Instead, take the time to examine the execution plan, comprehend the access method and investigate the optimizer’s choices. Better database performance begins with a clear understanding of what occurs behind the scenes of a query.
Top comments (0)