nest loop 在如下场景 性能会明显高于hash join
1、驱动结果集较小
2、被驱动表有高效索引
这种情况下，优化思路往nest loop方向优化

如下为一套数据库，top logicalread sql 中的一条sql，以此sql为例，来看下nest loop 如何优化

SQL_ID  c57g1tfcgrsyn, child number 0
-------------------------------------
select a.lost_rep_code,
       a.flow_code,
       a.deal_dept_name,
       a.deal_time,
       a.rep_dept_name,
       a.rep_time
  from (select a.lost_rep_code,
               b.flow_code,
               a.deal_dept_name,
               TO_CHAR(a.deal_time, 'yyyy-mm-dd HH24:mi:ss') AS deal_time,
               c.rep_dept_name,
               TO_CHAR(c.rep_time, 'yyyy-mm-dd HH24:mi:ss') AS rep_time,
               row_number() over(partition by b.flow_code order by a.deal_time desc) row_id
          from t_lost_deal_track a
          left join t_lost_flowcode b--被驱动表走了全表
            on a.track_id = b.track_id  --b表连接条件
          left join t_lost_report c
            on a.lost_rep_code = c.lost_rep_code
         where b.is_effective = '2'
           and b.find_status = '2'
           and a.lost_rep_code = :1
           and b.flow_code in ('0001')--b表谓词条件
         order by a.deal_time desc) a
 where a.row_id = 1

Plan hash value: 1862638321
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :1 (VARCHAR2(30), CSID=873): 'KD20161125879658'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"."ROW_ID"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "B"."FLOW_CODE" ORDER BY 
              INTERNAL_FUNCTION("A"."DEAL_TIME") DESC )<=1)
   3 - access("A"."TRACK_ID"="B"."TRACK_ID")
   4 - access("A"."LOST_REP_CODE"="C"."LOST_REP_CODE")
   6 - access("A"."LOST_REP_CODE"=:1)
   8 - access("C"."LOST_REP_CODE"=:1)
   9 - filter(("B"."FLOW_CODE"='0001' AND "B"."IS_EFFECTIVE"='2' AND "B"."FIND_STATUS"='2'))

---------------绑定变量后sql：
select a.lost_rep_code,
       a.flow_code,
       a.deal_dept_name,
       a.deal_time,
       a.rep_dept_name,
       a.rep_time
  from (select  a.lost_rep_code,
               b.flow_code,
               a.deal_dept_name,
               TO_CHAR(a.deal_time, 'yyyy-mm-dd HH24:mi:ss') AS deal_time,
               c.rep_dept_name,
               TO_CHAR(c.rep_time, 'yyyy-mm-dd HH24:mi:ss') AS rep_time,
               row_number() over(partition by b.flow_code order by a.deal_time desc) row_id
          from t_lost_deal_track a
          left join t_lost_flowcode b
            on a.track_id = b.track_id
          left join t_lost_report c
            on a.lost_rep_code = c.lost_rep_code
         where b.is_effective = '2'
           and b.find_status = '2'
           and a.lost_rep_code = 'KD20161125879658'
           and b.flow_code in ('0001')
         order by a.deal_time desc) a
 where a.row_id = 1

--分析：
1、执行计划id=9 走了T_LOST_FLOWCODE的全表扫描，cost为68169，而此sql的总cost为：68200 故此表的全表扫描是此sql的性能瓶颈
2、由于此处，驱动表t_lost_deal_track a 中有谓词条件：a.lost_rep_code = 'KD20161125879658' 故驱动结果集很小，从执行计划
id=4 ：row=2 也能看出，并且表T_LOST_FLOWCODE(track_id,flow_code)选择性很好，故根据： a.track_id = b.track_id b.flow_code in ('0001')
添加如下索引：
create index qms.i_lost_flowcode_002 on  qms.t_lost_flowcode(track_id,flow_code) online;


-- 优化后执行计划：
---------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 889378251
--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                     |     2 |   508 |    17  (12)| 00:00:01 |
|*  1 |  VIEW                            |                     |     2 |   508 |    17  (12)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK        |                     |     2 |   176 |    17  (12)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                     |       |       |            |          |
|   4 |     NESTED LOOPS                 |                     |     2 |   176 |    16   (7)| 00:00:01 |
|*  5 |      HASH JOIN OUTER             |                     |     2 |   146 |    11  (10)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_LOST_DEAL_TRACK   |     2 |    92 |     6   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | TRACK_LOST_REP_CODE |     2 |       |     4   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| T_LOST_REPORT       |     1 |    27 |     4   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | AK_KEY_2_T_LOST_R   |     1 |       |     3   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN            | I_LOST_FLOWCODE_002 |     1 |       |     2   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID  | T_LOST_FLOWCODE     |     1 |    15 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."ROW_ID"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "B"."FLOW_CODE" ORDER BY
              INTERNAL_FUNCTION("A"."DEAL_TIME") DESC )<=1)
   5 - access("A"."LOST_REP_CODE"="C"."LOST_REP_CODE"(+))
   7 - access("A"."LOST_REP_CODE"=:1)
   9 - access("C"."LOST_REP_CODE"(+)=:1)
  10 - access("A"."TRACK_ID"="B"."TRACK_ID" AND "B"."FLOW_CODE"='0001')
  11 - filter("B"."IS_EFFECTIVE"='2' AND "B"."FIND_STATUS"='2')
  
  
--优化效果：

21s-->0.03s