嵌套表外部表和内部表指定引起的逻辑读开销

1、使用leading和use_nl来设置表的查询顺序，来加快查询速度，一般把小表设为第一个表。
/*+LEADING(TABLE)*/
　　将指定的表作为连接次序中的首表.
/*+USE_NL(TABLE)*/
　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

成本计算方法：
设小表100行，大表100000行。

两表均有索引：
如果小表在内，大表在外(驱动表)的话，则扫描次数为：
100000+100000*2 (其中2表示IO次数，一次索引，一次数据)
如果大表在内，小表在外(驱动表)的话，则扫描次数为：
100+100*2.

两表均无索引：
如果小表在内，大表在外的话，则扫描次数为：
100000+100*100000
如果大表在内，小表在外的话，则扫描次数为：
100+100000*100

注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。
如果两个表都有索引，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。



################
# 使用HASH连接 #
################
SQL> select  /*+ ordered no_merge(tb) USE_HASH(ta) */
  2   *
  3  from (select rowid from t1 where val > 250) tb,
  4     t1 ta
  5  where ta.rowid = tb.rowid
  6  /

已选择56行。


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10037  consistent gets
          0  physical reads
          0  redo size
       2746  bytes sent via SQL*Net to client
        418  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         56  rows processed

################
# 使用嵌套连接 #
################
/*+USE_NL(TABLE)*/
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

SQL> select  /*+ ordered no_merge(tb) USE_NL(ta) */
  2   *
  3  from (select rowid from t1 where val > 250) tb,
  4     t1 ta
  5  where ta.rowid = tb.rowid
  6  /

已选择56行。


执行计划
----------------------------------------------------------
Plan hash value: 217642955

--------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |  1818 |   213K|  1822 |
|   1 |  NESTED LOOPS               |      |  1818 |   213K|  1822 |
|   2 |   VIEW                      |      |  1818 | 21816 |     4 |
|*  3 |    INDEX FAST FULL SCAN     | T1_I |  1818 | 29088 |     4 |
|   4 |   TABLE ACCESS BY USER ROWID| T1   |     1 |   108 |     1 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("VAL">250)

Note
-----
   - cpu costing is off (consider enabling it)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         86  consistent gets
          0  physical reads
          0  redo size
       2690  bytes sent via SQL*Net to client
        418  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         56  rows processed

这里使用tb作为外部表,ta作为内部表，tb的数据量小只有56条
换一下，使用ta作为外部表,tb作为内部表，发现一致读非常大，因为ta比tb大多了，ta有3000条记录
总结：使用嵌套连接时，小表作为外部表，大表作为内部表，并且最好在大表上建立索引，
      这样使用嵌套连接，效率非常高
如下是使用大表作为外部表：

SQL> select  /*+ ordered no_merge(tb) USE_NL(tb) */
  2   *
  3  from (select rowid from t1 where val > 250) tb,
  4     t1 ta
  5  where ta.rowid = tb.rowid
  6  /

已选择56行。


执行计划
----------------------------------------------------------
Plan hash value: 2026576436

---------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |  1818 |   213K|   969 |
|*  1 |  HASH JOIN             |      |  1818 |   213K|   969 |
|   2 |   VIEW                 |      |  1818 | 21816 |     4 |
|*  3 |    INDEX FAST FULL SCAN| T1_I |  1818 | 29088 |     4 |
|   4 |   TABLE ACCESS FULL    | T1   | 10000 |  1054K|   963 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TA".ROWID="TB".ROWID)
   3 - filter("VAL">250)

Note
-----
   - cpu costing is off (consider enabling it)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10037  consistent gets
          0  physical reads
          0  redo size
       2746  bytes sent via SQL*Net to client
        418  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         56  rows processed