3 ---------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ---------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 100 | 6800 | 233 (0)| 00:00:03 |
7 |* 1 | COUNT STOPKEY | | | | | |
8 | 2 | NESTED LOOPS | | | | | |
9 | 3 | NESTED LOOPS | | 101 | 6868 | 233 (0)| 00:00:03 |
10 | 4 | NESTED LOOPS | | 41 | 1927 | 28 (0)| 00:00:01 |
11 |* 5 | TABLE ACCESS FULL | X | 1 | 11 | 19 (0)| 00:00:01 |
12 | 6 | TABLE ACCESS BY INDEX ROWID| | 41 | 1476 | 9 (0)| 00:00:01 |
13 |* 7 | INDEX RANGE SCAN | | 41 | | 2 (0)| 00:00:01 |
14 |* 8 | INDEX RANGE SCAN | | 2 | | 2 (0)| 00:00:01 |
15 | 9 | TABLE ACCESS BY INDEX ROWID | | 2 | 42 | 5 (0)| 00:00:01 |
16 ---------------------------------------------------------------------------------------------------
如上执行计划,
要取100条记录,
由于如下步骤的结果集返回的估值是2.466
15 | 9 | TABLE ACCESS BY INDEX ROWID | | 2 | 42 | 5 (0)| 00:00:01 |
所以 连接他的步骤只需要返回100/2.466=41的结果集即可。
再评估子步骤的netsted loop,驱动的结果集为1, 所有被驱动的索引查询只需要返回41个结果集即可。
11 |* 5 | TABLE ACCESS FULL | X | 1 | 11 | 19 (0)| 00:00:01 |
即使如下步骤的索引范围扫描,一个索引值的平均记录是437,也只需要读取41条记录即可。
12 | 6 | TABLE ACCESS BY INDEX ROWID| | 41 | 1476 | 9 (0)| 00:00:01 |
nested loop 的最终返回结果集是两个子结果集的乘积。