Optimizer in Oracle

For all relational databases, the optimizer undoubtedly makes the core part of it, because the optimizer is responsible for parsing SQL, And we all access data stored in relational databases through SQL. The quality of all optimizers directly determines the strength of the relational database.

So what is Optimizer )? The optimizer is a built-in core subsystem in the ORACLE database. You can also think of it as a core module or a core function component in the ORACLE database. The optimizer aims to obtain the highest execution Path (Access Path) of the target SQL statement in the current situation according to certain judgment principles. That is to say, the optimizer aims to get the execution plan of the target SQL statement.

Oracle Optimizer is divided into RBO (Rlue-Based Optimizer: Rule-Based Optimizer) and CBO (Cost-Based Optimizer. in order to get the execution plan of the target SQL, the judgment principle used by RBO is a set of built-in rules which are hard-coded in the ORACLE database code, based on these rules, RBO selects one of the many possible execution plans of the target SQL statement as its execution plan, and the judgment principle used by CBO is cost, CBO selects the least cost from the many possible execution paths of the target SQL statement as its execution plan. The cost values of each execution path are based on the tables and indexes involved in the target SQL statement, the statistical information of related objects such as columns is calculated.

 

 

During the parsing process, ORACLE will check the syntax, semantics, and permissions of the target SQL statement.

 

As mentioned above, the rule-based optimizer (RBO) uses a series of fixed rules in the ORACLE database code to determine the execution plan of the target SQL statement. Specifically, ORACLE has set 1 to 15 levels, where 1 level indicates the highest execution efficiency, and 15 corresponding execution paths have the lowest execution efficiency. In ORACLE databases, for SQL statements corresponding to OLTP types, it is clear that ROWID is the most effective method for access, and full table scan is the lowest access efficiency. Therefore, the execution path of level 1 is "single row by rowid (using ROWID to access single row data)", and level 15 is "full table scan (full table scan )".

RBO has been in ORACLE for a long time. Although RBO is no longer supported by ORACLE since the beginning of ORACLE 10 Gb, its functions are not removed from ORACLE.