Oracle Optimization Learning

SQL execution efficiency has a significant impact on system usage, and this article summarizes some of the solutions to Oracle optimization problems that are encountered in troubleshooting problems, or the daily learning gains.

1. Oracle SQL execution Order

the parsing of SQL syntax is from right to left.

1.1 SQL steps to execute a statement

1) parsing, analyzing whether the syntax of the statement conforms to the specification, and measures the meaning of the expressions in the statement.

2) semantic analysis, check whether all the database objects involved in the statement exist, and the user has the appropriate permissions.

3) View transformation, converting a query statement involving a view into a corresponding base table query statement.

4) expression conversion, converting a complex SQL expression to a simpler equivalent join expression.

5) Select Optimizer, different optimizer generally produces different "execution plan"

6) Choose the connection method, Oracle has three kinds of connection methods, for multi-table connection Oracle can choose the appropriate connection mode.

7) Select the connection order, for multi-table connection ORACLE Select which pair of tables to connect first, select which table in both tables as the source data table.

8) Select the search path of the data, according to the above criteria to select the appropriate data search path, whether the use of full table search or index or other way.

9) Run the "Execution plan"

1.2 SQL Select statement Complete order of execution

1 . The FROM clause assembles data from different data sources;

2. Thewhere clause filters the record rows based on the specified criteria;

3 . The GROUP BY clause divides the data into multiple groupings;

4, using the aggregation function to calculate;

5, using having clauses to filter the grouping;

6, the calculation of all expressions;

7,Select the field;

8.Use order by to sort the result set.

The most obvious feature of the SQL language differs from other programming languages is the order in which the code is processed. In most database languages, code is processed in encoded order. In the SQL statement, however, the first child sentence to be processed from , rather than the first occurrence of a SELECT. sequence number of SQL query processing:

1 (8) SELECT (9) DISTINCT (11)

2 (1) from

3 (3) JOIN

4 (2) on

5 (4) WHERE

6 (5) GROUP by

7 (6) with {CUBE | ROLLUP}

8 (7) having

9 (Ten) ORDER by

each of these steps produces a virtual table that is used as input to the next step. These virtual tables are not available to callers ( client applications or external queries ) . Only the table generated in the last step will be given to the caller. If you do not specify a clause in the query, the corresponding step is skipped.

Introduction to the Logical query processing phase:

1. from: Performs a Cartesian product ( Cross join )on The first two tables in the FROM clause , Generate virtual table VT1.

2. on: Apply on filter to VT1 , only those that are true are inserted into TV2 .

3 outer (JOIN): outer JOIN ( cross JOIN inner JOIN) vt2 , generate TV3 from 1 3

4. where: apply where filter to TV3, only make true Line to insert TV4.

5 . Group by: groups The rows in the TV4 by the list of columns in the group by clause , generating TV5.

6, cute| ROLLUP: Inserts a super-group into the VT5to generate VT6.

7, having: apply A having filter to VT6 , only true Group into the VT7.

8 . Select: Process The select list and generate VT8.

9. DISTINCT: Delete duplicate lines from VT8 , product VT9.

Orderby: a row in VT9 is generated by a list of columns in an order by clause, a cursor ( VC10).

TOP: Select a specified number or proportion of rows from the beginning of the VC10, generate the table TV11, and returned to the caller.

2.   Oracle Execution Plan 2.1 Execution Order

according to Operation Indentation in the judgment, the indentation is the most first execution, (the indent phase, the top of the first execution).

same level if an action has no child The ID is executed first .

actions at the same level follow the most up-to-the-right first-execution principle .

Figure 3 1 execution plan diagram

Table access in several ways: (Not all)

• Table ACCESSFull (All-table scan)
• Table access by ROWID(accessed through ROWID tables)
• TABLE ACCESS by index Scan(index scans)

2.2 RBO and the CBO

the optimizer in Oracle is an optimization tool for SQL analysis and execution that is responsible for generating and developing an execution plan for SQL .

There are two types of Oracle optimizer:

• RBO(rule-based optimization) rule-based optimizer
• CBO(cost-based optimization) cost-based optimizer

RBO:

RBO has strict rules of use, as long as you follow this set of rules to write SQL statements, regardless of the contents of the data table, it will not affect your execution plan;

in other words, RBO is "insensitive "to Data, which requires SQL writers to be aware of the rules;

RBO has been used to Oracle 9i, starting with Oracle 10g ,RBO has been completely discarded.

CBO:

The CBO is a more reasonable and reliable optimizer than RBO, which completely replaces RBOin ORACLE 10g ;

The CBO calculates the " cost "of a variety of possible execution plans , the lowest implementation plan as the actual operation plan;

It relies on the statistics of database objects, the accuracy of statistics will affect The CBO makes the best choice, which is " sensitive "to the data.

2.3 INNER JOIN , Left join and the Right join

Inner joins within joins, returning only the equal data on both sides.

Left join , return data to the left as base table, match right table.

Right joins , the right side has returned data for the base table, and the left table matches.

when using left and right connections, be careful not to put on post conditions in where , otherwise it will be equivalent to inner connection.

Oracle Optimization Learning