关于USE_NL的使用

之前一直都不是很清楚这个怎么在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表。