Oracle optimizer and SQL query execution sequence

Before executing an SQL statement, Oracle needs to first check the SQL Execution Plan and then execute the SQL statement according to the execution plan. The optimizer is responsible for analyzing the execution plan, under different conditions, an SQL statement may have multiple execution plans. However, at a specific time point and in a specific environment, only one execution plan is optimal.

1. optimizer type:

There are two ways to optimize oracle: one is Rule-Based RBO (Rule-Based Optimization) and the other is Cost-Based CBO (Cost-Based Optimization ).

1. RBO: oracle follows the predefined rules of oracle during query execution.

2. CBO: Execution Plan Based on overhead or cost. The overhead here mainly refers to the use of CPU and memory. When determining whether this method is used, the optimizer mainly refers to the statistical information of tables and indexes. The statistical information shows the table size, the number of rows, and the length of each row. These statistics are not available in the database at first, but are only available after you perform analyze. In many cases, the Optimizer may make an incorrect execution plan when the statistics expire, because we should update this information in a timely manner. In Oracle8 and later versions, CBO is recommended for Oracle columns.

We need to make it clear that indexes are not necessarily optimal. For example, if a table has only two rows of data, I/O can complete the entire table search, at this time, the index requires two I/O operations. At this time, it is best to perform full table scan on the table.

Ii. optimizer optimization Mode)

The optimization modes include Rule, Choose, First rows, and All rows, which we mentioned above. I will explain it as follows:

Rule: a Rule-based approach.

Choolse: This is what we should note. By default, Oracle uses this method. When a table or index has statistical information, it adopts the CBO method. If the table or index does not have statistical information, the table is not particularly small, in addition, when the corresponding column has an index, the index is adopted and the RBO method is adopted.

First Rows: It is similar to the Choose method. The difference is that when a table has statistics, it returns the First few Rows of the query in the fastest way, the overall response time is reduced.

All Rows: This is what we call the Cost method. When a table has statistics, it returns All Rows of the table in the fastest way, improves the query throughput in general. If no statistical information is available, the rule-based approach is adopted.

Note: view the execution plan and press F5 in plsql. For example:

Select statement, GOAL = ALL_ROWS 7 4 72

Merge join cartesian 7 4 72

Table access full csms Z_STUDENT 3 2 18

Buffer sort 4 2 18

Table access full csms Z_CLASS 2 2 18

1 2 Next Page