About the sequence of Oracle execution plans

After studying Oracle for a long time, I would like to share with you that you will certainly have a lot of gains after reading this article. I hope this article will teach you more things. Each step of the Oracle execution plan returns a group of rows, which are used for the next step or returned to the user or application that issues the SQL statement at the last step. A group of rows returned by each step is called row source ). The tree chart below shows the data flow 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 will be briefly described ). Generally, this is not the order in which each step is executed.

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

The steps shown in the red box physically retrieve data from the data files in the database. This step is called an access path. The following describes the access paths that can be used in Oracle:
◆ Read all rows from the EMP table and the SALGRADE table respectively in steps 1 and 2.
◆ In step 3, search for each DEPTNO value returned by step 3 in the PK_DEPTNO index. It finds the ROWID of the rows associated with the DEPT table.
◆ In step 5, the rows returned by step 4th with the ROWID are retrieved from the DEPT table.

The steps shown in the black text box are performed on the row source, such as join, sort, or filter between two tables. Detailed descriptions will also be provided later:
◆ Implement nested loop operations in step 1 (equivalent to the nested loop in the C statement) and receive the row source from step 2 and step 3, connect each row from step 1 to the corresponding row in step 2, and return the result row to step 2.
◆ Complete a filter operation in step 1. It receives the row source from step 1 and step 2, removes the rows from step 2, and has corresponding rows in step 2, return the remaining rows from step 1 to the user or application that issues the statement.

Sequence of Oracle Execution Plan steps

Steps in the Oracle execution plan are not implemented in the order of their numbers: Oracle first implements the steps (such as steps 3, 5, and 6) that appear as leaves in the tree structure ). The row returned by each step is called the row source of the next step. Then Oracle implements the parent step.

For example, to execute the statements in Figure 5-1, perform these steps in sequence in the following Oracle columns:
First, implement step 3 in Oracle and return the result row to Step 3 in one row.
Oracle implements these steps for each row returned in Step 1:
◆ Step 5 of Oracle implementation, and return the result ROWID to Step 5.
◆ Step 4 of Oracle implementation, and return the result line to step 4.
◆ Step 2 of Oracle implementation will accept one line from step 2 and one line from step 2, and return it to step 2.
◆ Step 6 of Oracle implementation. If there is a result line, return it to Step 6.
◆ Oracle implements Step 1. If the row is returned from step 6, Oracle returns the row from step 1 to the user who issues the SQL statement.

Note that Oracle implements Steps 5, 4, 2, and 6 for each row returned by step 1. Many parent steps only need a single row from their child steps before they can be executed. For such a parent step, the parent step is implemented immediately as long as the child step has returned a single row (possibly the rest of the Oracle execution plan ). If the parent step of the parent step can also be activated through a single row, it will also be executed. Therefore, the execution can be connected in a tree, which may include the rest of the Oracle execution plan. For such operations, first_rows can be used as the optimization goal to quickly respond to user requests.

For each row retrieved by sub-steps in sequence, Oracle implements the parent step and all the steps connected together. The parent step triggered by each row returned by the sub-step includes table access, index access, nested loop connections, and filters.

Some parent steps require all rows from child steps before they are implemented. Oracle cannot implement this parent step until all rows return from the child step. Such parent steps include sorting, sorting and merging of connections, group functions, and total. For such an operation, first_rows cannot be used as the optimization goal, but all_rows can be used as the optimization goal to minimize the resource consumption of such operations.

Sometimes, when a statement is executed, it may run in parallel rather than in a step-by-step manner. For example, in the actual environment, steps 3, 5, and 4 may run in parallel to achieve better efficiency. From the tree chart above, it is difficult to see the sequence of operations executed, and another form of Oracle execution plan generated through Oracle, it is easy to see which operation is executed first and which is then executed. Such an Oracle execution plan is what we really need and will be detailed later. Now let's take a look at some preparations.

1. Some basic concepts of ORACLE execution plan
2. Improve query performance using the Oracle execution plan mechanism
3. Brief description of Oracle-encoded SQL
4. Code Description Oracle Selectivity
5. Oracle management tool-Overview of Oracle SQL Handler Functions