Steps to execute the plan
Each step of the execution plan returns a set of rows that are either used in the next step or, in the final step, returned to the user or application that issued the SQL statement. The set of rows returned by each step is called the row source. Figure 5-1 A tree diagram shows the flow of data from one walk to another. The numbering of each step reflects the order in which the steps are shown when you view the execution plan (how to view the execution plan is briefly explained). Generally this is not the order in which each step is performed. Each step of the execution plan either retrieves rows from the database or receives row data as input from one or more row sources:
The steps indicated by red boxes physically retrieve data from the data files in the database. This step is called an access path. The access paths that can be used in Oracle will be introduced in detail later:
Steps 3 and 6 read all rows from the EMP table and SALGRADE table respectively.
Step 5 looks for each DEPTNO value returned by step 3 in the PK_DEPTNO index. It finds out the ROWIDs of those rows associated with the DEPT table.
Step 4 retrieves from the DEPT table those rows whose ROWID is returned in step 5.
The steps indicated by the black text box operate on the row source, such as association, sorting, or filtering between 2 tables. Detailed introductions will be given later:
Step 2 implements nested loop operations (equivalent to nested loops in C statements), receives the row sources from steps 3 and 4, and compares each row from the source in step 3 with its row source in step 4. Corresponding rows are concatenated together and the resulting rows are returned to step 1.
Step 1 completes a filter operation. It receives the row sources from steps 2 and 6, eliminates those rows from step 2 that have corresponding rows in step 6, and returns the remaining rows from step 2 to the issuer. user or application.
Implement the sequence of execution plan steps
The steps in the execution plan are not implemented in the order in which they are numbered: Oracle first implements those steps that appear as leaves in the tree structure graph of Figure 5-1 (for example, steps 3, 5, and 6). The rows returned by each step are called the row sources of the next step. Oracle then implements the parent step.
For example, to execute the statement in Figure 5-1, Oracle implements these steps in the following order:
First, Oracle implements step 3 and returns the result rows to step 2, row by row.
For each row returned in step 3, Oracle implements these steps:
-- Oracle implements step 5 and returns the result ROWID to step 4.
-- Oracle implements step 4 and returns the result row to step 2.
-- Oracle implements step 2 and will accept a row from step 3 and a row from step 4 and return
Give step 1 a line.
-- Oracle implements step 6. If there is a result row, return it to step 1.
-- Oracle implements step 1. If a row is returned from step 6, Oracle returns the row from step 2 to
The user who issued the SQL statement.
Note that Oracle performs steps 5, 4, 2, and 6 once for each row returned by step 3. Many parent steps require only a single row from their child steps before they can execute. For such a parent step, the parent step (and possibly the rest of the execution plan) is implemented as soon as a single row has been returned from the child step. If the parent step's parent step can also be activated via a single row return, then it will also be executed. Therefore, executions can be chained up the tree, possibly including the remainder of the execution plan. For such operations, you can use first_rows as the optimization target to quickly respond to user requests.
For each row retrieved in turn by a child step, Oracle implements the parent step and all concatenated steps once. Parent steps that are triggered for each row returned by a child step include table access, index access, nested loop joins, and filters.
Some parent steps require all rows from child steps before they can be implemented. For such a parent step, Oracle cannot implement the parent step until all rows have been returned from the child step. Such parent steps include Sort, Sort-Merge Join, Group Function, and Total. For such an operation, first_rows cannot be used as the optimization target, but all_rows can be used as the optimization target to make this type of operation consume the least resources.
Sometimes when a statement is executed, it does not proceed step by step as mentioned above, but may run in parallel. For example, in an actual environment, steps 3, 5, and 4 may run in parallel to achieve better efficiency. From the tree diagram above, it is difficult to see the order in which each operation is executed. However, through another form of execution plan generated by ORACLE, it is easy to see which operation is executed first and which is executed later. This way The execution plan is what we really need, and will be explained in detail later.
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/lcyhjx/archive/2009/12/20/5044799.aspx
-