Oracle Corporation began the transition from rule-based optimization to cost-based optimization with the release of Oracle 7 in 1992. This architectural shift was designed to replace a rigid, heuristic-driven model with a mathematical system capable of analyzing data distribution and system resource availability. While the Rule-Based Optimizer (RBO) remained available for over a decade to ensure backward compatibility, the company officially deprecated the feature with the release of Oracle Database 10g in 2003.
Relational Query Optimization Mechanics within Oracle systems involve the internal processes used by the database engine to translate SQL queries into executable plans. During the decade-long migration, database administrators were required to move from manual query tuning, which relied on specific syntax structures to influence the RBO, to statistical management. In the cost-based era, the accuracy of the execution plan became dependent on the quality of metadata collected about table volumes, column cardinality, and data skewness.
Timeline
- 1992:Oracle 7 introduces the Cost-Based Optimizer (CBO) as an alternative to the established Rule-Based Optimizer.
- 1997:Oracle 8 expands CBO capabilities to support partitioned tables and complex object types, making rule-based logic increasingly inefficient for new features.
- 1999:Oracle 8i introduces the
DBMS_STATSPackage, providing more sophisticated statistics collection than the traditionalANALYZECommand. - 2001:Oracle 9i introduces features such as bind variable peeking and automated system statistics, further distancing the engine from rule-based logic.
- 2003:Oracle 10g is released with the CBO as the only officially supported optimizer. The RBO remains in the code for internal use but is no longer enhanced or recommended for production workloads.
Background
The history of Relational Query Optimization Mechanics is rooted in the early development of relational database management systems (RDBMS). Before the 1990s, most database engines utilized rule-based systems. These systems relied on a fixed hierarchy of execution paths. When a query was submitted, the optimizer would check for the presence of certain structures, such as a primary key or a single-column index, and follow a predetermined rank to choose the execution path. This method was predictable but lacked the ability to adapt to varying data volumes or hardware capabilities.
The transition toward cost-based models was heavily influenced by the 1979 research of Patricia Selinger and her colleagues working on IBM's System R project. Selinger’s work established that an optimizer could estimate the cost of various execution plans by assigning numerical values to I/O operations and CPU cycles. Oracle adapted these concepts to create an optimizer that could evaluate multiple permutations of join orders and access methods, selecting the one with the lowest total estimated cost. This move was necessitated by the increasing complexity of enterprise data environments, where a rule-based approach often resulted in poor performance for large-scale joins and data warehousing applications.
Mechanics of the Rule-Based Optimizer
The RBO operated on a strict set of 15 ranking rules. The hierarchy dictated that the engine would always focus on a rowid-based access path over an index-based access path, and an index-based path over a full table scan, regardless of how many rows were actually being retrieved. For example, if a table contained 1,000,000 rows and a query requested 900,000 of them, the RBO would still use an available index if the syntax permitted it, even though a full table scan would be significantly faster.
Under RBO, developers had to carefully order the tables in theFROMClause of their SQL statements. The RBO typically processed tables from right to left, meaning the last table listed was often treated as the driving table in a join. This manual requirement placed a heavy burden on software developers to understand the internal physical structure of the database. Failure to order tables correctly or to use specific syntax patterns could result in exponential increases in execution time.
The Evolution of the Cost-Based Optimizer
The introduction of the CBO in Oracle 7 marked the beginning of a move toward automated performance management. The CBO functions by generating multiple potential execution plans and calculating a cost for each based on available statistics. The fundamental formula for cost estimation considers the number of blocks to be read from disk and the CPU processing required for sorting and filtering. As hardware evolved, Oracle updated the CBO to account for different I/O speeds and multi-core processing.
Key to the CBO’s efficacy is the use of histograms. Histograms allow the database to understand the distribution of data within a column. In a rule-based system, the optimizer assumes data is distributed uniformly. However, in real-world scenarios, data is often skewed. For example, a column representing 'Country' might have millions of rows for 'USA' and only a few hundred for 'Iceland'. The CBO uses histograms to recognize this skew, choosing an index for 'Iceland' but opting for a full table scan for 'USA' to minimize I/O overhead.
Technological Advancements in Oracle 8i and 9i
During the middle of the migration period, Oracle introduced several features that solidified the CBO's dominance. Oracle 8i brought the concept of stored outlines, which allowed administrators to 'freeze' a specific execution plan generated by the CBO. This addressed one of the primary criticisms of cost-based systems: plan instability. Because the CBO relies on statistics, an update to those statistics could theoretically cause a plan to change unexpectedly. Stored outlines provided a bridge for organizations transitioning from the total predictability of RBO to the dynamic nature of CBO.
Oracle 9i introduced 'Peeking at Bind Variables.' In previous versions, if a query used bind variables (placeholders), the CBO could not determine the specific values and thus could not use histograms effectively. Bind variable peeking allowed the optimizer to look at the value provided during the first execution of a query to make a more informed cardinality estimation. This version also saw the introduction of dynamic sampling, where the optimizer could take a quick look at the data during the parse phase if statistics were missing or stale.
The Deprecation of RBO in Oracle 10g
By the release of Oracle 10g, the RBO was considered an obsolete technology. Technical manuals from this era indicate that the RBO was not updated to support any features introduced after Oracle 7, such as bitmap indexes, function-based indexes, or parallel query execution. While the RBO code remained in the binary to support internal dictionary queries and legacy applications, it was officially unsupported for general use. The default value for theOPTIMIZER_MODEInitialization parameter was set toALL_ROWS, which is a CBO-only mode.
Benchmarks conducted during this transition period demonstrated the performance gap between the two models. In complex star-schema joins typical of data warehouses, the CBO often outperformed the RBO by several orders of magnitude because it could use hash joins. The RBO was limited primarily to nested loop joins and sort-merge joins, which are less efficient for massive datasets where large portions of tables must be compared. The CBO’s ability to intelligently choose between these algorithms based on the estimated size of intermediate result sets was the primary driver for its adoption.
Impact on Database Administration
The migration from RBO to CBO changed the daily activities of database administrators. Under RBO, tuning was a reactive process of rewriting SQL or adding hints to force certain behaviors. In the CBO era, the focus shifted to the proactive maintenance of statistics. The introduction of automated statistics gathering jobs in Oracle 10g further simplified this process. Administrators became responsible for ensuring that theDBMS_STATSPackage was running correctly and that the statistics accurately reflected the state of the data. This shift allowed for more scalable database environments, as the engine could automatically adapt to changing data patterns without manual intervention from developers or administrators.
The transition from rules to costs represented a fundamental change in the philosophy of data management, moving from human-prescribed logic to machine-calculated optimization.
Today, Relational Query Optimization Mechanics continue to evolve with features like adaptive query optimization in Oracle 12c and later versions. These modern systems can change an execution plan mid-stream if they detect that the actual cardinality of a result set differs significantly from the initial estimation. This level of sophistication is a direct result of the foundational work performed during the decade-long migration from the rule-based models of the early 1990s to the cost-based models that define modern database performance.
