oracle走错索引不出结果

有一个脚本跑了很久不出结果，优化之后瞬间出结果。原语句如下：
SQL> explain plan for
  2  select *
  3        from crm_dg.tb_ba_channelstaff      a,
  4             crm_dg.tb_ba_subscription_hist b,
  5             crm_dg.tb_cm_serv              c
  6       where a.subs_id = b.subs_id
  7         and b.serv_id = c.serv_id
  8         and a.create_date >= to_date('20150201', 'yyyymmdd')
  9         and c.acc_nbr = '15322926784';

Explained.

Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1257311340

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                              |     1 |   562 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                              |     1 |   562 |    12   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN         |                              |     2 |   716 |     8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | PROD_INST                    |     1 |   273 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | IX_PROD_INST_NUM             |     1 |       |     3   (0)| 00:00:01 |
|   5 |    BUFFER SORT                 |                              |     2 |   170 |     4   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| TB_BA_CHANNELSTAFF           |     2 |   170 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | IDX_BA_CHANNELSTAFF_CRT_DATE |     2 |       |     2   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS BY INDEX ROWID  | ORDER_ITEM_HIST              |     1 |   204 |     2   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN           | PKH_ORDER_ITEM               |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   4 - access("ACC_NBR"='15322926784')
   7 - access("A"."CREATE_DATE">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - filter("SERV_ID"="PROD_INST_ID")
   9 - access("A"."SUBS_ID"="ORDER_ITEM_ID")


......getting segment size......

OWNER                SEGMENT_NAME                   SEGMENT_TYPE           Size(Mb)
-------------------- ------------------------------ -------------------- ----------
CRM_DG               IX_PROD_INST_NUM               INDEX                  602.0625
CRM_DG               IDX_BA_CHANNELSTAFF_CRT_DATE   INDEX                 1799.5625
CRM_DG               PKH_ORDER_ITEM                 INDEX                      6199
CRM_DG               PROD_INST                      TABLE                      5126
CRM_DG               TB_BA_CHANNELSTAFF             TABLE                      7390
CRM_DG               ORDER_ITEM_HIST                TABLE                     48776

6 rows selected.

Elapsed: 00:00:01.26
......getting table infomation......

OWNER                TABLE_NAME                       Size(Mb) PAR DEGREE       NUM_ROWS GLO STATS GATHER TIME
-------------------- ------------------------------ ---------- --- ---------- ---------- --- ------------------
CRM_DG               *PROD_INST                     3958.84835 NO           1   15205690 YES         7.84770833
CRM_DG               PROD_INST                      3958.84835 NO           1   15205690 YES         7.84770833
CRM_DG               *TB_BA_CHANNELSTAFF            5265.49083 NO           1   64956086 YES         102.696563
CRM_DG               TB_BA_CHANNELSTAFF             5265.49083 NO           1   64956086 YES         102.696563
CRM_DG               *ORDER_ITEM_HIST               40876.7086 NO           1  210109488 YES         10.4260532
CRM_DG               ORDER_ITEM_HIST                40876.7086 NO           1  210109488 YES         10.4260532

6 rows selected.

Elapsed: 00:00:01.20
......getting index infomation......

OWNER                INDEX_NAME                     TABLE_NAME                     PAR UNIQUENES DEGREE     INDEX_TYPE LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
-------------------- ------------------------------ ------------------------------ --- --------- ---------- ---------- ----------- ---------- ----------------- ----
CRM_DG               IDX_BA_CHANNELSTAFF_CRT_DATE   TB_BA_CHANNELSTAFF             NO  NONUNIQUE 1       NORMAL      84968          2          50669112  36.412511
CRM_DG               IX_PROD_INST_NUM               PROD_INST                      NO  NONUNIQUE 1       NORMAL      37438          2          12501881        100
CRM_DG               PKH_ORDER_ITEM                 ORDER_ITEM_HIST                NO  UNIQUE    1       NORMAL     399394          2         166506822        100


这里c和b表都是视图。
最后的结果只有2条记录。返回数据量少，可以考虑嵌套循环走索引。
IDX_BA_CHANNELSTAFF_CRT_DATE非常差的选择性，而且将近1.8G非常大，索引扫描单块读，非常慢。
为了避免走IDX_BA_CHANNELSTAFF_CRT_DATE，这里用了no_index这个hint，oracle自动选择了关联列的索引，而且是主键索引PK_CHANNELSTAFF_SUBS_ID，基本上瞬间出结果。




以下是优化后的语句：

SQL> explain plan for
  2  select /*+leading(c,b) use_nl(c,b) no_index(a,IDX_BA_CHANNELSTAFF_CRT_DATE)*/*
  3        from crm_dg.tb_ba_channelstaff      a,
  4             crm_dg.tb_ba_subscription_hist b,
  5             crm_dg.tb_cm_serv              c
  6       where a.subs_id = b.subs_id
  7         and b.serv_id = c.serv_id
  8         and a.create_date >= to_date('20150201', 'yyyymmdd')
  9         and c.acc_nbr = '15322926784';

Explained.

Elapsed: 00:00:00.09
SQL> @getplan
'general,outline,starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3198218290

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |     1 |   562 |    39   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                         |     1 |   562 |    39   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                         |    16 |  7632 |    18   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PROD_INST               |     1 |   273 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_PROD_INST_NUM        |     1 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| ORDER_ITEM_HIST         |    16 |  3264 |    14   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IXH_ORDERITEM_SERVID    |    16 |       |     2   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID | TB_BA_CHANNELSTAFF      |     1 |    85 |     2   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN          | PK_CHANNELSTAFF_SUBS_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   4 - access("ACC_NBR"='15322926784')
   6 - access("SERV_ID"="PROD_INST_ID")
   7 - filter("A"."CREATE_DATE">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - access("A"."SUBS_ID"="ORDER_ITEM_ID")
SQL>