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 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>