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

Iii. execution sequence of SQL statements

1. SQL statement execution steps:
1) analyze the syntax and analyze whether the syntax of the statement complies with the specifications, to measure the meaning of each expression in the statement.
2) perform Semantic Analysis to check whether all database objects involved in the statement exist and the user has the corresponding permissions.
3) view conversion: converts query statements related to views into corresponding query statements for base tables.
4) expression conversion: converts complex SQL expressions into simple equivalent join expressions.
5) Select the optimizer. Different optimizers generally generate different "execution plans"
6) Select the connection mode. Oracle has three connection modes. You can select an appropriate connection mode for multi-Table Oracle connection.
7) Select the connection sequence, select which table to connect to Oracle for multi-Table connection, and select which table in the two tables as the source data table.
8) Select the data search path and select the appropriate data search path based on the preceding conditions. If you choose full table search or index or other methods.
9) run the "Execution Plan"
2. Oracle sharing principles:
Oracle stores the executed SQL statements in the shared buffer pool of the memory, which can be shared by all database users. When you execute an SQL statement (sometimes called a cursor) if it is exactly the same as the previously executed statement, Oracle can quickly obtain the parsed statement and the best execution path. this function greatly improves SQL Execution performance and saves memory usage.
Iii. How to improve query efficiency using Oracle statements: 1: Where column in (select * from... where ...); 2 :... where exists (select 'x' from... where ...); the second format is far more efficient than the first one. In Oracle, almost all in operator subqueries can be rewritten to use exist for subqueries using exists. The Oracle system first checks the primary query, then run the subquery until it finds the first match, which saves time. When the Oracle system executes the in subquery, it first executes the subquery, and store the obtained result list in a temporary table with an index.
Avoid having clauses. Having clauses are used to filter result sets only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced.

3. Complete execution sequence of SQL select statements:

1) The from clause assembles data from different data sources;
2) The WHERE clause filters record rows based on specified conditions;
3) The Group by clause divides data into multiple groups;
4) use aggregate functions for computation;
5) use the having clause to filter groups;
6) Calculate all expressions;
7) Use order by to sort the result set.

4. Multi-table join query sequence:

For example:

Select * from a inner join B on A. Bid = B. ID inner join C on B. cid = C. ID where a. xxx = 'xxx'

1). From A, B, and C as data sources

2). Use Where a. xxx = 'xxx' to filter data in table.

3) Compare table A and table B in sequence, and filter data sets that meet the conditions based on the On condition.

4) compare the result set obtained from tables A and B with the records in Table C to filter the qualified data sets.
5). Completed

Note:

When multi-table join queries are performed, which two tables are combined for query first? The CBO mode is used for analysis by the optimizer. Generally, many data is placed in the table, while few data is placed out. Here we assume that the sorting is A, B, and C.