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. various declarations of the OPTIMIZER_MODE parameter in the ora file, such as RULE, COST, CHOOSE, ALL_ROWS, 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 a CBO. Otherwise, the database will adopt a RULE Optimizer.
By default, ORACLE uses the CHOOSE Optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE Optimizer, the Optimizer based on rules or costs is directly used.
2. Access Table
ORACLE uses two methods to access table records:
A. Full table scan
Full table scan refers to sequential access to each record in the table. ORACLE optimizes full table scan 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 establish a connection between data and the physical location where data is stored (ROWID. generally, indexes provide a quick way to access ROWID, so those queries based on index columns can improve the performance.
