Analyze how to use the Oracle optimizer to get twice the result with half the effort

This article analyzes how to use the Oracle optimizer to get twice the result with half the effort.

1. select an appropriate Oracle optimizer

There are three optimizer types in Oracle:

A. RULE (RULE-based) B. COST (COST-based) c. CHOOSE (selectivity ).

Set the default optimizer. You can declare the OPTIMIZER_MODE parameter in the init. ora file, such as RULE, COST, CHOOSE, ALL_ROWS, and FIRST_ROWS. Of course, you also overwrite SQL statements or sessions.

To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database.

If the optimizer mode of the database is set to CHOOSE, the actual optimizer mode is related to whether the analyze command has been run. If the table has been analyze, the optimizer mode will automatically become CBO. Otherwise, the database will adopt the RULE form optimizer.

By default, Oracle uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, instead, we directly use a rule-based or cost-based optimizer.

2. Access Table

Oracle uses two methods to access table records:

A. Full table scan:

Full table scan accesses each record in the table sequentially. Oracle optimizes full table scanning by reading multiple data blocks at a time.

B. Access the table through ROWID:

You can use ROWID-based access to improve the efficiency of accessing tables. ROWID contains the physical location information recorded in the table. Oracle uses indexes to connect data with the physical location where data is stored (ROWID. Generally, indexes provide a quick way to access ROWID. Therefore, queries based on index columns can improve the performance.

1 2 Next Page

[Content navigation]
Page 1st: analyzes how to use the Oracle optimizer to get twice the result with half the effort	Page 2nd: analyzes how to use the Oracle optimizer to get twice the result with half the effort