有一个脚本跑了很久不出结果，优化之后瞬间出结果。原语句如下：SQL> explain plan for 2 select * 3from crm_dg.tb_ba_channelstaffa, 4crm_dg.tb_ba_subscription_hist b, 5crm_dg.tb_cm_servc 6where a.subs_id = b.subs_id 7and b.serv_id = c.serv_id 8and a.create_date >= to_date('20150201', 'yyyymmdd') 9and c.acc_nbr = '15322926784';Explained.Elapsed: 00:00:00.03SQL> @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……OWNERSEGMENT_NAMESEGMENT_TYPESize(Mb)——————– —————————— ——————– ———-CRM_DGIX_PROD_INST_NUMINDEX602.0625CRM_DGIDX_BA_CHANNELSTAFF_CRT_DATE INDEX1799.5625CRM_DGPKH_ORDER_ITEMINDEX6199CRM_DGPROD_INSTTABLE5126CRM_DGTB_BA_CHANNELSTAFFTABLE7390CRM_DGORDER_ITEM_HISTTABLE487766 rows selected.Elapsed: 00:00:01.26……getting table infomation……OWNERTABLE_NAMESize(Mb) PAR DEGREENUM_ROWS GLO STATS GATHER TIME——————– —————————— ———- — ———- ———- — ——————CRM_DG*PROD_INST3958.84835 NO1 15205690 YES7.84770833CRM_DGPROD_INST3958.84835 NO1 15205690 YES7.84770833CRM_DG*TB_BA_CHANNELSTAFF5265.49083 NO1 64956086 YES102.696563CRM_DGTB_BA_CHANNELSTAFF5265.49083 NO1 64956086 YES102.696563CRM_DG*ORDER_ITEM_HIST40876.7086 NO1 210109488 YES10.4260532CRM_DGORDER_ITEM_HIST40876.7086 NO1 210109488 YES10.42605326 rows selected.Elapsed: 00:00:01.20……getting index infomation……OWNERINDEX_NAMETABLE_NAMEPAR UNIQUENES DEGREEINDEX_TYPE LEAF_BLOCKSBLEVEL CLUSTERING_FACTOR——————– —————————— —————————— — ——— ———- ———- ———– ———- —————– —-CRM_DGIDX_BA_CHANNELSTAFF_CRT_DATE TB_BA_CHANNELSTAFFNO NONUNIQUE 1NORMAL84968250669112 36.412511CRM_DGIX_PROD_INST_NUMPROD_INSTNO NONUNIQUE 1NORMAL37438212501881100CRM_DGPKH_ORDER_ITEMORDER_ITEM_HISTNO UNIQUE 1NORMAL3993942166506822100这里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)*/* 3from crm_dg.tb_ba_channelstaffa, 4crm_dg.tb_ba_subscription_hist b, 5crm_dg.tb_cm_servc 6where a.subs_id = b.subs_id 7and b.serv_id = c.serv_id 8and a.create_date >= to_date('20150201', 'yyyymmdd') 9and c.acc_nbr = '15322926784';Explained.Elapsed: 00:00:00.09SQL> @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>
只要笑一笑，没什么事请过不了
