Three types of Oracle Database Optimizer

Personally, I like Oracle very much. In actual work, I also like Oracle Database optimizer experience and lessons. Let's discuss this in detail! If you are a loyal fan of Oracle Database optimizer optimization, do not miss it.

There are three types of Oracle Database optimizer:

A. RULE (based on Rules)

B. COST (COST-based)

C. CHOOSE (optional)

To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must regularly update statistics to ensure the accuracy of object statistics in the Oracle database.

If the Oracle database optimizer mode 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.

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 scan by reading multiple data blocks at a time.

B. index scan: You can use ROWID-based access to improve table access efficiency. 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.

Oracle has two access modes for indexing.

A) index unique scan (index unique scan) In most cases, the optimizer accesses the INDEX through the WHERE clause.

The LOADING table has two indexes: the unique index LOADING_PK on the LOADING column and the non-unique index IDX_MANAGER on the MANAGER column.

 
 

  
  
SELECT loading  
  
  
FROM LOADING  
  
  
WHERE LOADING = ‘ROSE HILL’;  
 
 

Internally, the preceding SQL statement is executed in two steps. First, the LOADING_PK index is accessed by unique index scanning to obtain the corresponding ROWID, use ROWID to access the table and perform the next retrieval. if the returned columns are included in the INDEX column, the Oracle database optimizer will not perform the second step (access the table through ROWID ). because the retrieval data is stored in the index, accessing the index alone can fully meet the query results. the following SQL statements only require the INDEX UNIQUE SCAN operation.

 
 

  
  
SELECT LOADING  
  
  
FROM LOADING  
  
  
WHERE LOADING = ‘ROSE HILL’;  
 
 

B) INDEX RANGE query (INDEX RANGE SCAN) is applicable to two situations:

1. a range-based search

2. Non-unique index-based retrieval

The WHERE clause condition includes a series of values. Oracle will query LODGING_PK through index range query. the index range query returns a set of values, which is less efficient than the unique index scan. the SQL statement is executed in two steps. The index range query of IDX_MANAGER (obtain the ROWID of all matching records) and the next step is to obtain the value of the LOADING column through the ROWID access table.

Because IDX_MANAGER is a non-unique index, Oracle databases cannot perform unique index scanning on it. because the SQL statement returns the LOADING column, which does not exist in the IDX_MANAGER index, an operation is performed to access the table through ROWID after the index range query. in the WHERE clause, if the first character of the value corresponding to the index column starts with the WILDCARD (WILDCARD), the index will not be used. in this case, Oracle uses full table scan. the Oracle Database optimizer is described above.