Oracle走错索引不出结果

有一个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 487766 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.42605326 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>更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址