In this lesson, you will learn how a database engine calculates the "price" of an execution plan to choose the most efficient path for your query.
The Logic of Cost
When you submit a SQL query, the database doesn't just run it; it evaluates multiple valid algebraic ways to retrieve the requested data. A Cost-Based Optimizer (CBO) acts like a GPS system. Just as a GPS compares different routes based on distance and traffic, the CBO assigns a numerical "cost" to various execution plans. This cost represents an estimate of the resources required—specifically CPU cycles, memory usage, and I/O operations—to complete the task.
How the Calculation Works
The engine uses the Statistics it has gathered about your tables (like the number of rows, the distribution of values, and index density) to estimate the cost.
Consider a simple JOIN between two tables, Orders and Customers. The optimizer might calculate two potential plans:
- Nested Loop Join: Suitable if
Ordersis very small and has an index on the join key. The cost is calculated based on the number of lookups required. - Hash Join: Suitable if both tables are large. The cost is calculated based on the memory required to build the hash table and the overhead of scanning the data.
The CBO compares these:
-- The optimizer evaluates:
-- Plan A (Nested Loop): 1,000 units of I/O
-- Plan B (Hash Join): 250 units of I/O
-- Result: The Optimizer chooses Plan B.
SELECT * FROM Orders
JOIN Customers ON Orders.cust_id = Customers.id;
If your table statistics are outdated—for instance, if you recently added a million rows but haven't updated the statistics—the engine might incorrectly "guess" that a nested loop is cheaper, leading to a query that hangs for hours. The cost is only as accurate as the data the engine has about your set. By assigning these values, the database attempts to minimize the total "time-to-result" by selecting the plan with the lowest cumulative cost.
Key Takeaway: A Cost-Based Optimizer predicts the resource consumption of different execution strategies using table statistics, choosing the plan with the lowest mathematical cost to ensure efficient data retrieval.