ORACLE SQL performance optimization series (1)_PHP tutorial

1. Choose a suitable ORACLE optimizer
There are three types of ORACLE optimizers:
a. RULE (rule-based) b. COST (cost-based) c. CHOOSE (selective)
Set the default The optimizer can pass various declarations of the OPTIMIZER_MODE parameters in the init.ora file, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. Of course, you can also override it at the SQL sentence level or session level.
In order to use the cost-based optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of the object statistics in the database.
If the optimizer mode of the database is set is selectivity (CHOOSE), then the actual optimizer mode will be related to whether the analyze command has been run. If the table has been analyzed, the optimizer mode will automatically become CBO, otherwise, the database will use the RULE form of the optimizer.
By default, ORACLE uses the CHOOSE optimizer. In order to avoid unnecessary full table scans, you must try to avoid using the CHOOSE optimizer and directly use rule-based or cost-based optimizers.
2. Ways to access Table
ORACLE uses two ways to access records in the table:
a. Full table scan
Full table scan is to sequentially access each record in the table. ORACLE uses one read Optimize the full table scan by entering multiple data blocks (database block).
b. Access the table through ROWID
You can use the ROWID-based access method to improve the efficiency of accessing the table. The ROWID includes the table Recorded physical location information. ORACLE uses an index (INDEX) to realize the connection between the data and the physical location (ROWID) where the data is stored. Usually the index provides a method to quickly access the ROWID, so those queries based on the index column can get Performance improvement.