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