The execution plan sequence does not comply with general rules.

? In Oracleperformancetuningguide, the execution plan sequence is described as the rightmost and rightmost execution first, and then the parent step is executed, that is, the rightmost step is executed first. If it is of the same level, then, execute the parent step first (TheexecutionorderinEXPLAINPLANoutputbeginswith ).

? In the Oracle performance tuning guide, the execution plan sequence is described as the rightmost and rightmost execution first, and then the parent step is executed, that is, the rightmost step is executed first. If it is of the same level, then execute The parent step first (The original document: The execution order in explain plan output begins

? In the Oracle performance tuning guide, the execution plan sequence is described as the rightmost and rightmost execution first, and then the parent step is executed, that is, the rightmost step is executed first. If it is of the same level, then execute The parent step first (the original document: the execution order in explain plan output begins with The line that is? Furthest indented to the right. The next step is the parent of that line. If two lines? Are indented equally, then the top line is normally executed first ).

? ? ? In practice, this rule is not completely correct. The execution sequence of the SQL internal steps of ORACLE differs from that shown in the plan. If you do not carefully analyze the steps and believe in documents, you may feel confused. For exampleScalary subquery)The display of the execution plan is very confusing, for example:

SQL> select * from; ? ? ? ? ID NAME ---------------- ? ? ? ? ? 1 ? ? ? ? ? 2 B ? ? ? ? ? 3 c 3 rows selected. SQL> select * from B; ? ? ? ? ID NAME ---------------- ? ? ? ? ? 1x1 ? ? ? ? ? 2x2 2 rows selected. SQL> SELECT a. ID, a. NAME, (SELECT B. id from B WHERE a. ID = B. ID )? Bid FROM; Execution Plan -------------------- Plan hash value: 2657529235 ------------------------- | Id? | Operation? ? ? ? | Name | Rows? | Bytes | Cost (% CPU) | Time? ? | ------------------------- |? 0 | select statement? |? ? ? |? ? 3 |? ? 60 |? ? 3? (0) | 00:00:01 | | *? 1 |? Table access full | B? ? |? ? 1 |? ? 13 |? ? 3? (0) | 00:00:01 | |? 2 |? Table access full |? ? |? ? 3 |? ? 60 |? ? 3? (0) | 00:00:01 | ------------------------- Predicate Information (identified by operation id ): ----------------- ? ? 1-filter ("B". "ID" =: B1)

? ? ?? According to the analysis of the document, it is clear that ID = 2 is of the same level as ID = 1, and ID = 1 is on ID = 2, then the execution plan should be in the order of 1 --> 2 --> 0. However, obviously this is not the order under analysis. a must be obtained. a. id to find B. Use "B". "ID" =: B1As you can see,: B1, Similar to binding variables, there are two tables here, and the query by SQL must come from A. ID. Therefore, the scheme of scalar quantum query should be 2 --> 1 --> 0, and the operations of 2 and 1 are similar to those of nested loops (The difference is that the driving table of the scalar query is an inner table.), Each row of A will execute B once. Of course, there must be Optimizations in ORACLE. This kind of optimization will cache the matched. ID value. If the value is the same, B is not scanned repeatedly. You can go to DBMS_XPLAN.DISPLAY_CURSOR to see how to execute it:

SQL> @ display_cursor SQL _ID? Caq6tcx266xnq, child number 1 ------------- SELECT a. ID, a. NAME, (SELECT B. id from B WHERE a. ID = B. ID) bid FROM Plan hash value: 2657529235 ---------------------------- | Id? | Operation? ? ? ? | Name | Starts | E-Rows | A-Rows |? A-Time? | Buffers | ---------------------------- |? 0 | select statement? |? ? ? |? ? ? 1 |? ? ? ? |? ? ? 3 | 00:00:00. 01 |? ? ? 8 | | *? 1 |? Table access full | B? ? |? ??? 3? |? ? ? 1 |? ? ? 2 | 00:00:00. 01 |? ? ? 21 | |? 2 |? Table access full |? ? |? ? ? 1 |? ? ? 3 |? ??? 3?| 00:00:00. 01 |? ? ? 8 | ---------------------------- Predicate Information (identified by operation id ): ----------------- ? ? 1-filter ("B". "ID" =: B1)

? ? ?? A has three rows in total and B is accessed three times. B is returned two rows because one row does not match.. Because here. there is no duplicate value for ID, and A row of id = 1 is inserted below, because id = 1 already exists in Table A. Therefore, the scalar query has A cache, So scanning B is three times, instead of four, as shown below:

SQL> INSERT INTO a VALUES (1, 'D '); 1 row created. SQL> COMMIT; Commit complete. SQL> ?? @ Display_cursor SQL _ID? Caq6tcx266xnq, child number 0 ------------- SELECT a. ID, a. NAME, (SELECT B. id from B WHERE a. ID = B. ID) bid FROM Plan hash value: 2657529235 ---------------------------- | Id? | Operation? ? ? ? | Name | Starts | E-Rows | A-Rows |? A-Time? | Buffers | ---------------------------- |? 0 | select statement? |? ? ? |? ? ? 1 |? ? ? ? |? ? ? 4 | 00:00:00. 01 |? ? ? 8 | | *? 1 |? Table access full | B? ? |? ???3? |? ? ? 1 |? ? ?2?| 00:00:00. 01 |? ? ? 21 | |? 2 |? Table access full |? ? |? ? ? 1 |? ? ? 4 |? ??? 4?| 00:00:00. 01 |? ? ? 8 | ---------------------------- Predicate Information (identified by operation id ): ----------------- ? ? 1-filter ("B". "ID" =: B1)

? ? ? We can see from the plan that although A is 4 rows, it is because of distinct. ID is 3 rows, so still scan B 3 times, where ID = 1 access is cache result, through the A-ROWS can see B or return 2 rows, instead of 3 rows. Therefore, we do not think that scalar quantum queries are inefficient. scalar quantum queries are similar to filters. If we can perform INDEX scans on scalar quantum queries, or even unique index scan, if the primary table queries a large number of rows with duplicate values, the efficiency is still very high. To some extent, scalar queries eliminate the JOIN operation and often query the rows corresponding to a table, it is more efficient to match a column value in another table than to JOIN (of course, since it is similar to the nested loops, The result set is certainly not very large, otherwise the efficiency will be poor, the efficient design of tom is explained in detail ).
? ?
? ? This article mainly explains that the execution plan reflects the SQL Execution sequence, but if you know the execution sequence in the SQL statement accurately through the execution plan, you don't just need to understand the rules described in the document, in practical application, you may encounter such problems. Of course, there are few errors in the document, but most of the documents are about general rules. SO, in the learning process, questions that you do not understand must be questioned and discussed at any time.

Original article address: The Execution Plan sequence does not comply with the general rules. Thank you for sharing it with the original author.