嵌套循环连接(nested loops join)
访问次数：驱动表返回几条，被驱动表访问多少次。
驱动表是否有顺序：有。
是否要排序：否。
应用场景： 1.关联中有一个表比较小；
2.被关联表的关联字段上有索引；
3.索引的键值不应该重复率很高。
如果你做过开发，就把它看成两层嵌套的for循环。
下面我们来做个实验：
SQL> create table test1 as select * from dba_objects where rownum <=100;
SQL> create table test2 as select * from dba_objects where rownum <=1000;
SQL> exec dbms_stats.gather_table_stats(user,'test1');SQL> exec dbms_stats.gather_table_stats(user,'test2');
SQL> alter session set statistics_level=all;
SQL> select /*+leading(t1) use_nl(t2)*/count(*) 2 from test1 t1, test2 t2 3 where t1.object_id = t2.object_id; COUNT(*) ---------- 100 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------- SQL_ID 3v5gu7ppdsz67, child number 0 ------------------------------------- select /*+leading(t1) use_nl(t2)*/count(*) from test1 t1, test2 t2 where t1.object_id = t2.object_idPlan hash value: 1459699139
----解释一下：
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。 A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。 Buffers为每一步实际执行的逻辑读或一致性读。 Reads为物理读。 OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。 0/1/M 为最优/one-pass/multipass执行的次数。 Used-Mem耗的内存
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1504 | | 2 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.01 | 1504 | | 3 | TABLE ACCESS FULL| TEST1 | 1 | 100 | 100 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| TEST2 | 100 | 1 | 100 |00:00:00.01 | 1500 |---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 4 - filter("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 in (10, 11, 12); COUNT(*) ---------- 3 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------- SQL_ID 0skx6hyjtsncu, 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 in (10, 11, 12) --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 49 | | 2 | NESTED LOOPS | | 1 | 3 | 3 |00:00:00.01 | 49 | |* 3 | TABLE ACCESS FULL| TEST1 | 1 | 3 | 3 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| TEST2 | 3 | 1 | 3 |00:00:00.01 | 45 | --------------------------------------------------------------------------------------- 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)