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.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......OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb)-------------------- ------------------------------ -------------------- ----------CRM_DG IX_PROD_INST_NUM INDEX 602.0625CRM_DG IDX_BA_CHANNELSTAFF_CRT_DATE INDEX 1799.5625CRM_DG PKH_ORDER_ITEM INDEX 6199CRM_DG PROD_INST TABLE 5126CRM_DG TB_BA_CHANNELSTAFF TABLE 7390CRM_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.84770833CRM_DG PROD_INST 3958.84835 NO 1 15205690 YES 7.84770833CRM_DG *TB_BA_CHANNELSTAFF 5265.49083 NO 1 64956086 YES 102.696563CRM_DG TB_BA_CHANNELSTAFF 5265.49083 NO 1 64956086 YES 102.696563CRM_DG *ORDER_ITEM_HIST 40876.7086 NO 1 210109488 YES 10.4260532CRM_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.412511CRM_DG IX_PROD_INST_NUM PROD_INST NO NONUNIQUE 1 NORMAL 37438 2 12501881 100CRM_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.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>