Oracle SQL statement execution sequence

Oracle SQL statement execution sequence

 The SQL syntax is analyzed from right to left.
 
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"
 
Ii. oracle sharing principles:
    ORACLE stores the executed SQL statements in the shared buffer pool, 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 parsed statements 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 subqueries using EXISTS.
With EXIST, the Oracle system first checks the primary query, and then runs the subquery until it finds the first match,
This saves time. When the Oracle system executes the IN subquery, it first executes the subquery and stores the obtained result list IN a temporary table with an index.
 
Avoid having clauses. HAVING filters the result set 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.
 
Iv. 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. select fields;
8. Use order by to sort the result set.
Unlike other programming languages, SQL is most characteristic of code processing. In most database languages, codes are processed in the encoding order. However, in an SQL statement, the first clause to be processed is FROM, rather than the first SELECT statement to appear. Sequence Number of the SQL Query Process:
(8)
1SELECT(9) DISTINCT (11) <TOP_specification> <select_list>
2(1)FROM <left_table> 
3(3) <join_type> JOIN <right_table>
4(2) ON <join_condition>
5(4) WHERE <where_condition>
6(5) group by <group_by_list>
7(6) WITH {CUBE | ROLLUP}
8(7) HAVING
9 (10) order by <order_by_list>
Each of the preceding steps generates a virtual table, which is used as the input for the next step. These virtual tables are unavailable to callers (client applications or external queries. Only the table generated in the last step will be sent to the caller. If a clause is not specified in the query, the corresponding steps are skipped.
Introduction to the logical query processing phase:
1. FROM: Perform Cartesian Product (cross join) on the first two tables in the FROM clause to generate a virtual table VT1.
2. ON: Apply the ON filter to VT1. Only those that are true are inserted into TV2.
3. OUTER (JOIN): If outer join is specified (compared with cross join or inner join), the row that does not match is retained in the table and added to VT2 as an external row to generate tv3. If the FROM clause contains more than two tables, perform steps 1 to 3 on the result table generated by the previous join and the next table until all the table locations are processed.
4. WHERE: Apply the WHERE filter to TV3 and insert TV4 only when the row is set to true.
5. group by: groups rows in TV4 BY column list in the group by clause to generate tv5.
6. CUTE | ROLLUP: inserts the supergroup into VT5 to generate VT6.
7. HAVING: Apply the HAVING filter to VT6. Only groups that make true are inserted to VT7.
8. SELECT: process the SELECT list and generate VT8.
9. DISTINCT: deletes duplicate rows from VT8, product VT9.
10. order by: ORDER the rows in VT9 in the column list in the order by clause to generate a cursor (VC10 ).
    11. TOP: select a specified number or proportion of rows from the beginning of VC10, generate the table TV11, and return it to the caller.