Chat Step order of Oracle Execution plan

After a long time learning Oracle, so share with you, after reading this article you certainly have a lot of harvest, hope this article can teach you more things. Each step of the Oracle execution plan returns a set of rows that are either used for the next step, or returned to the user or application that issued the SQL statement at the end. The set of rows returned by each step is called the row source (row
SOURCE). The tree chart below shows the flow of data from one step to another. The number of each step reflects the sequence of steps shown when you observe the Oracle Execution plan (how to observe the Oracle execution plan is briefly described). In general, this is not the order in which each step is executed.

Each step of the Oracle execution plan either retrieves rows from the database or receives row data from one or more row sources as input:

The steps indicated by the red box physically retrieve data from the data file in the database. This step is referred to as the access path, which details the access paths available to Oracle in the following sections:

Steps 3rd and 6th read all rows from the EMP table and the Salgrade table, respectively.

The 5th step looks for each DEPTNO value returned by step 3 in the Pk_deptno index. It finds the rowid of those rows associated with the Dept table.

The 4th step retrieves the rows returned by rowID for step 5th from the Dept table.

The steps indicated by the black box are manipulated on the line source, such as making an association between 2 tables, sorting, or filtering, followed by a detailed description:

The 2nd step implements a nested loop operation (equivalent to a nested loop in the C statement), receives a row source from steps 3rd and 4th, joins each row from the 3rd step source with the corresponding row in its 4th step, and returns the result line to the 1th step.

The 1th step is to complete a filter operation. It receives the row source from steps 2nd and 6th, removes the rows from the 2nd step, has the corresponding rows in 6th step, and returns the remaining rows from the 2nd step to the user or application that issued the statement.

Implementing the sequence of Oracle execution plan steps

The steps in the Oracle execution plan are not implemented in the order in which they are numbered: Oracle first implements the steps that appear as leaves in the tree structure graph (for example, steps 3, 5, 6). The row returned by each step is called the row source for its next step. Then Oracle implements the parent step.

For example, in order to execute the statements in Figure 5-1, Oracle implements these steps in the following order: first, Oracle implements step 3 and returns the resulting rows to step 2nd in a row. For each row returned by step 3rd, Oracle implements these steps:

Oracle implements step 5 and returns the result rowID to step 4th.

Oracle implements step 4 and returns the resulting row to the 2nd step.

Oracle implements Step 2, accepts a row from step 3rd and a row from step 4th, and returns to the 1th step line.

Oracle Implementation Step 6, if there is a result line, return it to the 1th step.

For Oracle Implementation Step 1, if you return a row from step 6, Oracle returns the row from step 2nd to the user who issued the SQL statement.

Note that Oracle implements step 5,4,2,6 once for each row that is returned by step 3rd. Many parent steps require only a single line from their child steps before they can be executed. For such a parent step, the parent step is implemented as soon as the child step has returned a single row (and possibly the remainder of the Oracle execution Plan). If the parent step of the parent step can also be activated by returning a single row, it is also executed. Therefore, execution can be concatenated on the tree and may contain the remainder of the Oracle execution plan. For such operations, you can use first_rows as the optimization target to enable fast response to user requests.

For each row that is retrieved sequentially from each child step, Oracle implements the parent step and all of the steps that are concatenated together. The parent steps that are triggered for each row returned by a child step include table access, index access, nested looping joins, and filters.

Some parent steps require all rows from a child step before they are implemented. For such a parent step, Oracle cannot implement the parent step until all rows are returned from the child step. Such parent steps include sorting, sorting a merged connection, group functionality, and totals. For such an operation, first_rows should not be used as an optimization target, but can use all_rows as the optimization target, so that the operation of the type is the least expensive.

Sometimes when a statement is executed, it is not done as mentioned above, but it is possible to run in parallel, as in the actual environment, 3, 5, 4 steps may run in parallel in order to achieve better efficiency. From the tree chart above, it is difficult to see the sequence of execution of each operation, and another form of Oracle execution plan generated by Oracle, it is easy to see which operation is executed first, which is executed, so the Oracle execution plan is what we really need, and will give detailed instructions later. Now let's look at some preliminary knowledge.