Oracle Ranking Scheme for SQL

This list describes the amount of time that is required by Oracle to perform a query. The fastest row access by ROWID happens very quickly since Oracle knows the exact data block where the row resides. The slowest, a full-table scan, could take hours to execute, depending upon the number of rows in the table. The following list reflects the fastest to slowest time required to perform a query:

1. Single row access by ROWID
2. Single row by cluster join
3. Single row by hash cluster with unique or primary key
4. Single row by unique or primary key
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Using a multi-column (concatenated) index
9. Using a single column index
10. A bounded index range scan
11. A full-table scan

In Oracle databases, it's common to hear the assertion that a full-table scan is the most expensive operation that can be performed. While it's true that full-table scans can be resource-intensive, they are not inherently the most costly operation in all situations. The cost or efficiency of a database operation is highly context-dependent and varies based on the nature of the data, the specific query, the structure of the table, and the overall database design. A full-table scan involves reading every row in a table to identify those that satisfy a query. It can be resource-intensive because it might require a large amount of disk I/O, particularly for large tables. However, there are scenarios where a full-table scan is the most efficient operation. For instance, if a query needs to retrieve a significant proportion of the rows in a table, a full-table scan could be faster than an index-based retrieval.
Index operations can sometimes be more expensive than full-table scans, especially in scenarios where a large number of random I/O operations are required. This might happen when a query must retrieve a high percentage of rows from a large table, and the rows are not adjacent on disk. In such cases, using an index can result in many disk I/O operations to retrieve the individual rows, which can be slower than reading all the rows in a continuous sequence as in a full-table scan.
Additionally, certain complex operations such as sorting, aggregations, and certain types of joins can be resource-intensive, depending on the amount and distribution of data involved. Similarly, operations involving large LOBs (Large Objects) or extensive manipulation of CLOBs and BLOBs can be quite costly. So, while full-table scans can be expensive, it's not accurate to categorically state that they are the most expensive operation to execute on an Oracle table. The true cost of a database operation depends on a variety of factors, and different operations can be more or less efficient depending on the specific circumstances. Oracle's Cost-Based Optimizer takes these factors into account when deciding the best execution plan for a query.

In Oracle Database systems, the Rule-Based Optimizer (RBO) has been deprecated. Oracle has shifted its focus towards the Cost-Based Optimizer (CBO), which is more advanced and efficient for query optimization. The Rule-Based Optimizer was the original optimization strategy used in earlier versions of Oracle. It made decisions based on a fixed set of rules, which determined the execution path of a query. However, the RBO lacked the ability to consider the actual data distribution and system resources, which often led to suboptimal execution plans. On the other hand, the Cost-Based Optimizer, introduced in Oracle 7 and significantly enhanced in subsequent releases, takes into account the statistical information about the data, such as the number of rows in tables, data distribution, index selectivity, and the system's physical characteristics. This statistical approach allows the CBO to evaluate multiple potential execution plans and choose the one with the lowest estimated cost in terms of system resources.
With the advent and continuous improvement of the CBO, Oracle officially deprecated the Rule-Based Optimizer. Starting from Oracle Database 10g, RBO is no longer supported, and Oracle strongly recommends using CBO for all databases for optimal performance and support. As an Oracle DBA, it is crucial to ensure that the database statistics are regularly gathered and updated, as the efficiency of the CBO heavily relies on the accuracy of these statistics. Proper management of statistics helps in generating the most efficient execution plans for SQL queries, thereby enhancing overall database performance.

The Oracle optimizer has two primary modes of operation:

1. cost-based or
2. rule-based (has been deprecated)

To set the optimizer goal, you can specify CHOOSE (for cost-based) or RULE (for rule-based) for the OPTIMIZER_MODE parameter in your database's init.ora file. You can override the optimizer's default operations at the query and session level.
Setting OPTIMIZER_MODE to RULE invokes the rule-based optimizer (RBO), which evaluates possible execution paths and rates the alternative execution paths based on a series of syntactical rules. In general, the RBO is seldom used by new applications, and is found primarily in applications developed and tuned for earlier versions of Oracle.
Setting OPTIMIZER_MODE to CHOOSE invokes the cost-based optimizer (CBO). You can use the analyze command to generate statistics about the objects in your database. The generated statistics include the number of rows in a table and the number of distinct keys in an index. Based on the statistics, the CBO evaluates the cost of the available execution paths and selects the execution path that has the lowest relative cost. If you use the CBO, you need to make sure that you run the analyze command frequently enough for the statistics to accurately reflect the data within your database. If a query references tables that have been analyzed and tables that have not been analyzed, the CBO may decide to perform full table scans of the tables that have not been analyzed. To reduce the potential for unplanned full table scans, you should use either the RBO or the CBO consistently throughout your database.