Explanation of NESTEDLOOPSSEMI (semi-nested loop) in oracle execution plan

NESTEDLOOPSSEMI (semi-nested loop) EXISTS in the execution plan of the SQL statements that exist in subqueries of in and related subqueries of EXISTS ). The so-called NESTEDLOOPSSEMI (semi-nested loop) is theoutquerystopsevaluating... Number of) theresultsetofthe

In the Execution Plan of the SQL statements with in subqueries and the SQL statements with EXISTS-related subqueries, there is NESTED LOOPS SEMI (SEMI-NESTED loop ). The so-called nested loops semi (SEMI-NESTED loop) is the out query stops evaluating... ) The result set of

InSubquerySQL statement and ExistenceEXISTSOfSQL statementThere is a nested loops semi (SEMI-NESTED loop) in the execution plan ).

The so-calledNested loops semi (SEMI-NESTED loop)That is

The out query stops evaluating... The result set of the inner query when the first value is found.

That is to say, once the first result of the subquery is displayed, the primary query (the current row of the table in) stops executing the subquery.

Nested loops semi (SEMI-NESTED loop)The pseudocode for executing the process is as follows:

1. Open tab1 (Tables in the primary query)
2. While tab1 still has records
3. Fetch one record from tab1
4. (And) result = false (about variableSet the result value to alse.)
5. Open tab2
6. While tab2 still has records
7. Fetch one record from tab2
8. If (the result set obtained by executing a subquery statement based on the values of tab1.record and tab2.record is not empty) then
9. Result = true
10. (And)Exit loop2
    
11. End if
12. End loop2
    
13. Close tab2
14. If (result = true) return tab1 record
15. End loop1
    
16. Close tab1

Note:

Fetch one record from tab1

Result = false (about variableSet the result value to alse.)

Open tab2

These three statements are in a parallel relationship.

Result = true

Exit loop2

These two statements are in a parallel relationship.

SQL statement in an in statementNested loops semi (SEMI-NESTED loop) in the execution plan ):

[Html] view plaincopy

1. Gyj @ MYDB> set autot traceonly;
2. Gyj @ MYDB> select * from t4 where id in (select id from t3 );
4. 9 rows selected.
7. Execution Plan
8. ----------------------------------------------------------
9. Plan hash value: 1092212754
11. -----------------------------------------------------------------------------
12. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
13. -----------------------------------------------------------------------------
14. | 0 | select statement | 9 | 99 | 21 (0) | 00:00:01 |
15. | 1 | nested loops semi | 9 | 99 | 21 (0) | 00:00:01 |
16. | 2 | table access full | T4 | 9 | 54 | 3 (0) | 00:00:01 |
17. | * 3 | index range scan | IDX_T3 | 999K | 4882K | 2 (0) | 00:00:01 |
18. -----------------------------------------------------------------------------
20. Predicate Information (identified by operation id ):
21. ---------------------------------------------------
23. 3-access ("ID" = "ID ")
26. Statistics
27. ----------------------------------------------------------
28. 1 recursive cballs
29. 0 db block gets
30. 20 consistent gets
31. 0 physical reads
32. 0 redo size
33. 723 bytes sent via SQL * Net to client
34. 520 bytes encoded ed via SQL * Net from client
35. 2 SQL * Net roundtrips to/from client
36. 0 sorts (memory)
37. 0 sorts (disk)
38. 9 rows processed

ExistEXISTSOfSQL statementIn the Execution Plan):

[Html] view plaincopy

1. Open tab1
2. While tab1 still has records
3. Fetch record from tab1
4. Result = false
5. Open tab2
6. While tab2 still has records
7. Fetch record from tab2
8. If (tab1.record matches tab2.record) then
9. Result = true
10. Exit loop
11. End if
12. End loop
13. Close tab2
14. If (result = true) return tab1 record
15. End loop
16. Close tab1
    

Note:

1 #

The EXISTS predicate is very simple. It is a test of a non-empty set. If any row exists in its subquery, TRUE is returned; otherwise, FALSE is returned. The UNKNOWN result is not returned. The EXIST () predicate syntax is as follows: : = [NOTEXISTS] <表子查询>

2 #

In an execution plan, if a parent operation has two parallel sub-operations, one of the execution modes is:

The first sub-operation is executed first, which affects the execution of the sub-operation in the next column. That is to say, the first sub-operation traverses table A and the parent operation ends, when this sub-operation traverses data in A row of table A, another sub-operation traverses table B. For example,

1. |1 | nested loops semi | 9 | 99 | 21 (0) | 00:00:01 |
2. | 2 | table access full | T4 | 9 | 54 | 3 (0) | 00:00:01 |
3. | * 3 | index range scan | IDX_T3 | 999K | 4882K | 2 (0) | 00:00:01 |
   

Source: in-depth understanding of nested query

See:

[One question per day] OCP1z0-047: use of EXISTS in the subquery ................................ ..... 28