转载地址：http://www.cnblogs.com/ebs-blog/archive/2011/09/05/2167573.html
之前一直都不是很清楚这个怎么在Nest Loop中指定驱动表(外部表) 和内部表，经过了一些实验对这一部分有了一定的认识:
首先对于nest loop有两种机制: 内部表全表扫描和内部表索引扫描，9i开始对于内部表索引扫描的情况有存在两种方式: 内部表索引range scan & 内部表索引uniqu scan, 索引range   scan这种方式可以减少大的嵌套循环连接中的逻辑IO，减少锁存，甚至物理IO, 这个过程称为Table Prefetching.
另外对于nest loop, 驱动表和内部表位置如下:
1) 两个表全表扫描 :
      NESTED LOOPS
                TABLE ACCESS (FULL) OF 'Driving table'
                TABLE ACCESS (FULL) OF 'Inner table'
2) Inner 表索引扫描:
      NESTED LOOPS
                TABLE ACCESS (FULL) OF 'Driving table'
                INDEX RANGE SCAN OF 'Index of inner table'

问题一: 如果通过hint指定Nest loop的驱动表?
      1) /*+ ORDERED USE_NL(a b) */ 注意这时并非a 为驱动表， b为内部表，ORDERED和Where子句的顺序相关:
          drop table tmpa;
          drop table tmpb;
          create table tmpa as select rownum a, object_id b from DBA_objects where rownum<10000;
          create table tmpb as select rownum a, object_id b from user_objects where rownum<10;

          kl@k02> exec dbms_stats.gather_table_stats('kl','TMPB');

          PL/SQL procedure successfully completed.

          kl@k02> exec dbms_stats.gather_table_stats('kl','TMPB');

          PL/SQL procedure successfully completed.

          set autotrace traceonly exp stat

          ---- 该顺序可以看出驱动表TMPB是个小表， TMPA是个大表:
           kl@k02> select /*+ use_nl(a b) */ * from tmpa a,tmpb b where a.a=b.a;

          Execution Plan
          ----------------------------------------------------------
          0       SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=9 Bytes=144)
          1     0    NESTED LOOPS (Cost=38 Card=9 Bytes=144)
          2     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=9 Bytes=72)
           3     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=1 Bytes=8)

     ----use_nl(a b)
     kl@k02> select /*+ ordered use_nl(a b) */ * from tmpa a,tmpb b where a.a=b.a;

     Execution Plan
     ----------------------------------------------------------
        0       SELECT STATEMENT Optimizer=CHOOSE (Cost=20002 Card=9 Bytes=144)
        1     0    NESTED LOOPS (Cost=20002 Card=9 Bytes=144)
        2     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=9999 Bytes=79992)
        3     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=1 Bytes=8)


     ----顺序并没有变
     kl@k02> select /*+ ordered use_nl(b a) */ * from tmpa a,tmpb b where a.a=b.a;

     Execution Plan
     ----------------------------------------------------------
        0       SELECT STATEMENT Optimizer=CHOOSE (Cost=20002 Card=9 Bytes=144)
        1     0    NESTED LOOPS (Cost=20002 Card=9 Bytes=144)
        2     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=9999 Bytes=79992)
        3     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=1 Bytes=8)

      ---- 看看顺序实际是安from子句的顺序
       kl@k02> select /*+ ordered use_nl(b a) */ * from tmpb b,tmpa a where a.a=b.a;

       Execution Plan
       ----------------------------------------------------------
          0       SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=9 Bytes=144)
          1     0    NESTED LOOPS (Cost=38 Card=9 Bytes=144)
          2     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=9 Bytes=72)
          3     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=1 Bytes=8)


      ---- Leading可以选择inner table,这个地方我之前一直不清楚，目前看来Leading的表并非驱动表，而是内部表:
       kl@k02> select /*+ leading(b) use_nl(a) */ * from tmpb a,tmpa b where a.a=b.a;

       Execution Plan
       ----------------------------------------------------------
          0       SELECT STATEMENT Optimizer=CHOOSE (Cost=20002 Card=9 Bytes=144)
          1     0    NESTED LOOPS (Cost=20002 Card=9 Bytes=144)
          2     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=9999 Bytes=79992) --驱动表
          3     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=1 Bytes=8)



       kl@k02> select /*+ leading(a) use_nl(b) */ * from tmpb a,tmpa b where a.a=b.a;

       Execution Plan
       ----------------------------------------------------------
          0       SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=9 Bytes=144)
          1     0    NESTED LOOPS (Cost=38 Card=9 Bytes=144)
          2     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=9 Bytes=72)    ---驱动表
          3     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=1 Bytes=8)


问题二:
     如果没有统计信息，会怎样?
     kl@k02> exec dbms_stats.delete_table_stats('kl','TMPA');

     PL/SQL procedure successfully completed.

     kl@k02> exec dbms_stats.delete_table_stats('kl','TMPB');

     PL/SQL procedure successfully completed.

      kl@k02> select /*+ ordered use_nl(a b) */ * from tmpb a,tmpa b where a.a=b.a;

      Execution Plan
      ----------------------------------------------------------
         0       SELECT STATEMENT Optimizer=CHOOSE (Cost=1310 Card=1879 Bytes=97708)
         1     0    NESTED LOOPS (Cost=1310 Card=1879 Bytes=97708)
         2     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=327 Bytes=8502)
         3     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=6 Bytes=156)



      kl@k02> select /*+ ordered use_nl(a b) */ * from tmpb b,tmpa a where a.a=b.a;

      Execution Plan
      ----------------------------------------------------------
         0       SELECT STATEMENT Optimizer=CHOOSE (Cost=1310 Card=1879 Bytes=97708)
         1     0    NESTED LOOPS (Cost=1310 Card=1879 Bytes=97708)
         2     1      TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=327 Bytes=8502)
         3     1      TABLE ACCESS (FULL) OF 'TMPA' (Cost=4 Card=6 Bytes=156)

    ---- 可见如果没有统计信息，执行计划都是一样的；

问题三:
    如果有索引，内部表作index range scan
     kl@k02> create index b_ind on tmpa (a);

     Index created.

     kl@k02> exec dbms_stats.gather_table_stats('kl','TMPA');

     PL/SQL procedure successfully completed.

     kl@k02> exec dbms_stats.gather_table_stats('kl','TMPB');

     PL/SQL procedure successfully completed.

     kl@k02> select /*+ USE_NL(a b) */ * from tmpb b,tmpa a where a.a=b.a;

     Execution Plan
     ----------------------------------------------------------
        0       SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=9 Bytes=144)
        1     0    TABLE ACCESS (BY INDEX ROWID) OF 'TMPA' (Cost=2 Card=1 Bytes=8)
        2     1      NESTED LOOPS (Cost=20 Card=9 Bytes=144)
        3     2        TABLE ACCESS (FULL) OF 'TMPB' (Cost=2 Card=9 Bytes=72)
        4     2        INDEX (RANGE SCAN) OF 'B_IND' (NON-UNIQUE) (Cost=1 Card=1)
     (看到index range scan,并非card=1的表是驱动表。)

综上可以理解什么驱动表，什么是内部表，另外怎么指定驱动表，ordered hint指定from 后先出现的表为驱动表， leading hint 指定内部表。对于 HASH_JOIN也是类似，
HASH JOIN
        TABLE ACCESS (FULL) OF 'TMPB'    ---TMPB是HASH表
        TABLE ACCESS (FULL) OF 'TMPA'    ---TMPA是Prune表。

ITPub博客

关于USE_NL的使用（R0.1）

最新文章