SQL Tuning   «Prev 

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

Which Optimizer?

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.