ORACLE SQL performance optimization series (1)_PHP tutorial
Jul 13, 2016 pm 05:02 PM
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.

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Xiaomi 15 series full codenames revealed: Dada, Haotian, Xuanyuan

Performance comparison of different Java frameworks

The best time to buy Huawei Mate 60 series, new AI elimination + image upgrade, and enjoy autumn promotions

C++ program optimization: time complexity reduction techniques

How to optimize the performance of multi-threaded programs in C++?

Performance comparison of C++ with other languages

How good is the performance of random number generators in Golang?

How to interact with JSON data using SQL in Golang?
