Oracle optimizer and SQL query execution sequence

Oracle optimizer and SQL query execution sequence before Oracle executes an SQL statement, it first needs to check the SQL Execution Plan and then execute the SQL statement according to the execution plan, the optimizer executes the analysis of execution plans. under different conditions, an SQL statement may have multiple execution plans, but at a specific time point, in a specific environment, only one execution plan is optimal. I. optimizer type: There are two oracle Optimization methods, one is Rule-Based RBO (Rule-Based Optimization ), one 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 Oracle 8 and later versions, CBO is recommended for Oracle columns. Www.2cto.com, we need to understand that indexes are not necessarily optimal. For example, if a table only has two rows of data, I/O can complete the whole 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. 2. The Optermizer Mode includes four optimization modes: Rule, Choose, First rows, and All rows. I will explain it as follows: Rule: it is 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 www.2cto.com table access full csms Z_STUDENT 3 2 18 buffer sort 4 2 18 table access full csms Z_CLASS 2 2 2 18 III. SQL statement execution sequence 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, 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 principle: ORACLE stores the executed SQL statements in the shared buffer pool of the memory, it 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 the use of www.2cto.com in memory.
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, store 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.
3. Complete SQL Select statement execution sequence: 1). The from clause assembles data from different data sources; 2) the where clause filters record rows based on specified conditions. 3) group by clause divides data into multiple groups. 4) use aggregate functions for calculation. 5) use 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. bid = B. id inner join c on B. cid = c. id where. XXX = 'xxx' 1 ). from a, B, c as the data source www.2cto.com 2 ). through where. XXX = 'xxx' filter data in Table a 3 ). table a and table B are compared in sequence to filter qualified Datasets Based on the on condition. 4 ). compare the result set obtained from tables a and B with the records in table c, and filter the qualified data sets ). note: When performing multi-table join queries, which two tables are first combined with the query? The analysis by the optimizer adopts the CBO mode and usually stores many data in it, put less data on the outside. Here we assume that the sorting is a, B, and c.
Author dreamming_now