关于循环嵌套nested loops的一点分析

nested loops连接 /*+use_nl(tab01 tab02)*/

循环嵌套连接：oracle优化器根据基于rbo和cbo原则，选择两个表中一个为驱动表，也就是外部表，另一个作为内部表。注意hint写法中表如果用的别名，hint中必须写相应的别名，不然相应的hint不能生效。

在oracle9I后优化器推出两种执行方式，方式1先从外部表取第一行然后和内部表数据进行比较，此时会逐行取出数据，重复上述步骤直到外部表中所有记录全部处理完毕。

SQL> desc tab01;

Name Type Nullable Default Comments

---- ------------ -------- ------- --------

ID NUMBER Y

NAME VARCHAR2(10) Y

SQL> desc tab02;

Name Type Nullable Default Comments

---- ------------ -------- ------- --------

ID NUMBER Y

NAME VARCHAR2(10) Y

SQL> explain plan for select a.id,b.name from tab01 a,tab02 b

2 where a.id=b.id;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3710181165

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100 | 3300 | 2 (0)| 00:00:01

| 1 | NESTED LOOPS | | 100 | 3300 | 2 (0)| 00:00:01

| 2 | TABLE ACCESS FULL| TAB02 | 100 | 2000 | 2 (0)| 00:00:01

|* 3 | INDEX RANGE SCAN | INDEX_TAB01 | 1 | 13 | 0 (0)| 00:00:01

方式2 也是先从外部表取第一行然后和内部表数据进行比较，此时会记录内部表上相应的rowid，但是不会马上去内部表上取出数据，此时会继续从外部表继续取第二行数据然后获取rowid，直到所有数据获取完毕后，再对内部表进行索引扫描。

SQL> explain plan for select a.id,a.name,b.name from tab01 a,tab02 b where a.id=b.id

;

2 /

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1970217900

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100 | 4000 | 3 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| TAB02 | 1 | 20 | 1 (0)|

| 2 | NESTED LOOPS | | 100 | 4000 | 3 (0)|

| 3 | TABLE ACCESS FULL | TAB01 | 100 | 2000 | 2 (0)|

|* 4 | INDEX RANGE SCAN | INDEX_TAB02 | 1 | | 0 (0)|

--------------------------------------------------------------------------------

上述sql语句中cbo下oracle采取了nested loops循环嵌套，用tab01作为内部表，tab02作为外部表，注意执行计划中选择的是下面的表作为内部表，上面的为外部表，也就是驱动表。

由于外部表数据量比较小或者内部表上存在高效索引时，循环嵌套是比较高效的，循环嵌套连接能最快速地从结果集中提取第一批数据.其实rownum使用时，会产生一个count stopkey的计划行，解释为扫描表到前n行停止，当然这也可以当成很快的取出前一批数据的case。不过和nl意义却大不相同！

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1057886/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1057886/