nested loop驱动行源的选择

一般情况下，nested loop驱动行源应该选择小表，或者行数少的行源，但有时候也不一定。我们来看一个使用大表做驱动行源的例子。

1. 创建测试环境

大表test_big，小表test_small，二者通过test_big.object_id=test_small.object_id来关联。其中大表的object_id中会有重复记录，而小表中的object_id是唯一的，两个列上都有索引。

C:\Documents and Settings\yuechao.tianyc>sqlplus test/test

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 3月 29 22:37:13 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test_big as select * from dba_objects;

表已创建。

SQL> insert into test_big select * from test_big;

已创建50000行。

SQL> /

已创建100000行。

...

SQL> /

已创建6400000行。

SQL> commit;

提交完成。

SQL> create table test_small as select * from dba_objects where rownum < 5001;

表已创建。

SQL> create index ind_test_big on test_big(object_id);

索引已创建。

SQL> create index ind_test_small on test_small(object_id);

索引已创建。


2. 获取执行计划

分别使用大表和小表作为驱动行源，先看一下它们的执行计划：

-- 1. 使用test_big作为驱动行源
-- 执行步骤：（1）全表扫描test_big，得到行源A；（2）将A作为驱动行源，嵌套循环连接索引ind_test_small，得到行源B；（3）将行源B通过索引ind_test_small与表test_small关联，得到结果集。
SQL> explain plan for
  2  select/*+ordered use_nl(test_big,test_small)*/ count(test_big.object_name||test_small.object_name)
  3   from test_big, test_small where test_big.object_id = test_small.object_id;

已解释。

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan hash value: 3591390207

----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |   158 |    15M  (1)| 53:19:22 |
|   1 |  SORT AGGREGATE              |                |     1 |   158 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_SMALL     |     1 |    79 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                |  1247K|   187M|    15M  (1)| 53:19:22 |
|   4 |     TABLE ACCESS FULL        | TEST_BIG       |    14M|  1106M| 39134   (2)| 00:07:50 |
|*  5 |     INDEX RANGE SCAN         | IND_TEST_SMALL |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("test_big"."OBJECT_ID"="test_small"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement

已选择21行。

-- 2. 使用test_small作为驱动行源
-- 执行步骤：（1）全表扫描test_small，得到行源A；（2）将A作为驱动行源，嵌套循环连接索引ind_test_big，得到行源B；（3）将行源B通过索引ind_test_big与表test_big关联，得到结果集。
SQL> explain plan for
  2  select/*+ordered use_nl(test_big,test_small)*/ count(test_big.object_name||test_small.object_name)
  3   from test_small, test_big where test_big.object_id = test_small.object_id;

已解释。

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan hash value: 1952886871

----------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |   158 |  1098K  (1)| 03:39:42 |
|   1 |  SORT AGGREGATE              |              |     1 |   158 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_BIG     |   257 | 20303 |   226   (0)| 00:00:03 |
|   3 |    NESTED LOOPS              |              |  1247K|   187M|  1098K  (1)| 03:39:42 |
|   4 |     TABLE ACCESS FULL        | TEST_SMALL   |  4854 |   374K|    17   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | IND_TEST_BIG |   257 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("test_big"."OBJECT_ID"="test_small"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement

已选择21行。


3. 比较执行时间

比较上面两个不同的执行计划。看起来使用表test_small作为驱动行源更合理一些，它只有50001行，而表test_big有12800000行数据。而实际却不是这样，我们看一下他们各自的执行时间：

SQL> set timing on
-- 使用大表作为驱动行源
SQL> select/*+ordered use_nl(test_big,test_small)*/ count(test_big.object_name||test_small.object_name)
  2  from test_big, test_small where test_big.object_id = test_small.object_id;

COUNT(TEST1.OBJECT_NAME||TEST2.OBJECT_NAME)
-------------------------------------------
                                   12800000

已用时间:  00: 00: 36.70

-- 使用小表作为驱动行源
SQL> select/*+ordered use_nl(test1,test2)*/ count(test1.object_name||test2.object_name)
  2  from test2, test1 where test1.object_id = test2.object_id;

COUNT(TEST1.OBJECT_NAME||TEST2.OBJECT_NAME)
-------------------------------------------
                                   12800000

已用时间:  00: 02: 42.89


我们看到，使用大表作为驱动行源，耗时约36.7秒；而使用小表作为驱动行源，耗时达到2分42.89秒！

4. 原因

其实原因就在与大表的列object_id中有很多重复记录，当使用小表作为驱动行源时，全表扫描test_small的时间虽然很快，但对于test_small中的每一条记录，都对应test_big中的若干条记录，那么就需要根据查到的这些rowid，进行若干次的磁盘I/O来获得大表对应的数据。这里频繁的磁盘I/O就是问题的原因。因为test_big数据量很大，数据不可能存储在内存中。

而当使用大表作为驱动行源时，全表扫描test_big的时间虽然比较长，但每条记录都对应test_small中的一条记录，而且表test_small比较小，其数据及索引数据可以从内存中直接找到。这样耗费的时间就主要是全表扫描test_big的时间了，而这耗费不了太多的时间。在下面的测试中，只耗费了26.14秒：

SQL> select count(*) from test_big;

  COUNT(*)
----------
  12800000

已用时间:  00: 00: 26.14

SQL> set timing off
SQL> explain plan for
  2  select count(*) from test_big;

已解释。

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
--------------------------------------------------------------------------
Plan hash value: 3224830981

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 | 38982   (2)| 00:07:48 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_BIG |    14M| 38982   (2)| 00:07:48 |
-----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

已选择13行。



5. 备注

这里只是一个小测试，来说明nested loop中，驱动行源的选择要根据实际情况而定，不是一成不变的。其实这个SQL使用hash join是最快的：

SQL> explain plan for
  2  select count(test_big.object_name||test_small.object_name)
  3  from test_big, test_small where test_big.object_id = test_small.object_id;

已解释。

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan hash value: 1810242240

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |   158 | 39404   (3)| 00:07:53 |
|   1 |  SORT AGGREGATE     |            |     1 |   158 |            |          |
|*  2 |   HASH JOIN         |            |  1247K|   187M| 39404   (3)| 00:07:53 |
|   3 |    TABLE ACCESS FULL| TEST_SMALL |  4854 |   374K|    17   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_BIG   |    14M|  1106M| 39134   (2)| 00:07:50 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST_BIG"."OBJECT_ID"="TEST_SMALL"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement

已选择20行。

SQL> set timing on
SQL> select count(test_big.object_name||test_small.object_name)
  2  from test_big, test_small where test_big.object_id = test_small.object_id;

COUNT(TEST_BIG.OBJECT_NAME||TEST_SMALL.OBJECT_NAME)
---------------------------------------------------
                                            1280000

已用时间:  00: 00: 21.42