Oracle Ranking Scheme for SQL

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.

This list describes the amount of time that is required by Oracle to perform a query. T he 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

The Oracle optimizer has two primary modes of operation:

1. cost-based or
2. rule-based.

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.