嵌套循环连接(nested loops join)原理(二)
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T1"."OBJECT_ID"=10 OR "T1"."OBJECT_ID"=11 OR
"T1"."OBJECT_ID"=12))
4 - filter((INTERNAL_FUNCTION("T2"."OBJECT_ID") AND
"T1"."OBJECT_ID"="T2"."OBJECT_ID"))
SQL> select /*+leading(t1) use_nl(t2)*/count(*)
2 from test1 t1, test2 t2
3 where t1.object_id = t2.object_id
4 and t1.object_id =10;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID 24g0zhvczyf2h, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/count(*) from test1 t1, test2 t2 where
t1.object_id = t2.object_id and t1.object_id =10
Plan hash value: 1459699139
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 19 |
|* 3 | TABLE ACCESS FULL| TEST1 | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 4 | TABLE ACCESS FULL| TEST2 | 1 | 1 | 1 |00:00:00.01 | 15 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_ID"=10)
4 - filter("T2"."OBJECT_ID"=10)