oracle嵌套循环连接外部表和内部表的识别
SQL> create table a1 as select * from all_objects ;
Table created
SQL> select count(*) from a1;
COUNT(*)
----------
49708
SQL> create table a2 as select * from a1 where rownum<=10000;
Table created
SQL> analyze table a1 computer statistics;
SQL> analyze table a2 computer statistics;
1 嵌套连接
select/*+use_nl(a1,a2)*/a1.object_name
from a1,a2
where a1.object_id=a2.object_id
下面无法看出那个是外部表和内部表，据说数量小的表或者结果集当外部表，然后读一条外部表的数据去全表扫内部表。听说倒数第二个表就是外部表。
已选择10000行。
已用时间: 00: 01: 09.95
执行计划
----------------------------------------------------------
Plan hash value: 2866307826
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 312K| 1338K (2)| 04:27:46 |
| 1 | NESTED LOOPS | | 10000 | 312K| 1338K (2)| 04:27:46 |
| 2 | TABLE ACCESS FULL| A1 | 49708 | 1359K| 140 (3)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| A2 | 1 | 4 | 27 (4)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6414326 consistent gets
0 physical reads
0 redo size
278675 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
2 增加个索引
create index ix_a2_type on a2(object_type);
select/*+use_nl(a1,a2)*/a1.object_name from a1,a2 where a1.object_id=a2.object_id and a2.object_type='INDEX'
执行计划
----------------------------------------------------------
Plan hash value: 4121744415
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 333 | 12654 | 46021 (2)| 00:09:13 |
| 1 | NESTED LOOPS | | 333 | 12654 | 46021 (2)| 00:09:13 |
| 2 | TABLE ACCESS BY INDEX ROWID| A2 | 333 | 3330 | 22 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_A2_TYPE | 333 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | A1 | 1 | 28 | 138 (3)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A2"."OBJECT_TYPE"='INDEX')
4 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
657969 consistent gets
4 physical reads
0 redo size
27368 bytes sent via SQL*Net to client
1078 bytes received via SQL*Net from client
65 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
956 rows processed
这里A1和A2调换了位置。从PLSQL DEVELOPER工具视图来看 Depth 为3的先读取A2的索引IX_A2_TYPE
select count(a2.object_name ) from a2 where a2.object_type='INDEX'
为956行。那么我们可以确定这个理论 “数据量小的表或者结果集当外部表” 关于这条理论“倒数第二个表就是外部表”尚不清楚，
因为第一条语句的计划如果A1做外部表的话就违反了 数据量小 这条定律。
3增加ORDERD 提示 注意该提示是指定FROM后面的表顺序来决定连接顺序的，这次A2连接A1。
select/*+ordered use_nl(a1,a2)*/a1.object_name from a2,a1 where a1.object_id=a2.object_id
已选择10000行。
已用时间: 00: 01: 13.70
执行计划
----------------------------------------------------------
Plan hash value: 3613923551
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 312K| 1381K (2)| 04:36:16 |
| 1 | NESTED LOOPS | | 10000 | 312K| 1381K (2)| 04:36:16 |
| 2 | TABLE ACCESS FULL| A2 | 10000 | 40000 | 29 (4)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| A1 | 1 | 28 | 138 (3)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6881435 consistent gets
0 physical reads
0 redo size
278675 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
到这里可以确定倒数第二个就是外部表，虽然于定律发生冲突，那只是优化器得到的路径是错误，也就是执行计划是错误的，需要人工干预！
4 给A1表建索引
create index ix_a1_type on a1(object_type);
select/*+ use_nl(a2,a1)*/a1.object_name from a2,a1 where a1.object_id=a2.object_id and a1.object_type='SYNONYM'
该索引返回同义词行数为：20026 而下面的执行机会行数为1420行。千万别搞错了执行计划第4行的A2表Rows=1 意思是每次匹配一行数据返回。
总共要匹配1420次，当为什么是1420行呢？为什么不是20026行呢？ 如果是20026 那么A2应该是10000行当外部表的资格啊
已用时间: 00: 00: 58.64
执行计划
----------------------------------------------------------
Plan hash value: 2348548291
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1420 | 56800 | 38305 (2)| 00:07:40 |
| 1 | NESTED LOOPS | | 1420 | 56800 | 38305 (2)| 00:07:40 |
| 2 | TABLE ACCESS BY INDEX ROWID| A1 | 1420 | 51120 | 63 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_A1_TYPE | 1420 | | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | A2 | 1 | 4 | 27 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A1"."OBJECT_TYPE"='SYNONYM')
4 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2584503 consistent gets
0 physical reads
0 redo size
74804 bytes sent via SQL*Net to client
2277 bytes received via SQL*Net from client
174 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2592 rows processed
查看具体值
select count(a1.object_name ) from a2,a1 where a1.object_id=a2.object_id and a1.object_type='SYNONYM'
结果数据量是：2592
那么说执行计划这次判断正确了，索引返回来的数量少于1万行，唯独计算数量的时候偏少了。
因为它是这样算的 行数除以density
select num_rows,distinct_keys,num_rows/distinct_keys from user_ind_statistics where index_name='IX_A1_TYPE'
|
NUM_ROWS |
DISTINCT_KEYS |
NUM_ROWS/DISTINCT_KEYS |
|
49708 |
35 |
1420.22857142857 |
同样
select num_rows,distinct_keys,num_rows/distinct_keys from user_ind_statistics where index_name='IX_A2_TYPE'
|
NUM_ROWS |
DISTINCT_KEYS |
NUM_ROWS/DISTINCT_KEYS |
|
10000 |
30 |
333.333333333333 |
结论：可以确定嵌套循环二定律是 数量少的为外部表（也就是传说中的驱动表，不专业的说法） 外部表在计划中是倒数第二个表。
5 再来看下
select/*+ordered use_nl(a1,a2)*/a1.object_name from a2,a1 where a1.object_id=a2.object_id and a1.object_type='SYNONYM'
已选择2592行。
已用时间: 00: 00: 02.93
执行计划
----------------------------------------------------------
Plan hash value: 3029564842
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1420 | 56800 | 20055 (1)| 00:04:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A1 | 1 | 36 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1420 | 56800 | 20055 (1)| 00:04:01 |
| 3 | TABLE ACCESS FULL | A2 | 10000 | 40000 | 29 (4)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_A1_ID_TYPE | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OBJECT_TYPE"='SYNONYM')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10928 consistent gets
0 physical reads
0 redo size
74804 bytes sent via SQL*Net to client
2277 bytes received via SQL*Net from client
174 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2592 rows processed
那么说来这个计划是错误的 索引返回1420条而A2表是1万条，强行指定连接提示也会导致错误执行计划。这里是把索引当作了内部表！扫描一万次索引。