sql tuning之變通

今天翻了下和朋友的聊天記錄,看到以前最佳化過的一條sql,比較有意思,語句大概是這樣的(表和欄位被我修改過),
select
count(DISTINCT id),
       code
  FROM log
WHERE rtime >= to_date('20081101','yyyymmdd')
   AND rtime <= to_date('20081131','yyyymmdd')
   AND source_code = 7
   AND tag = '0'
GROUP BY code
這個log表大概有2億條記錄,rtime上有個單獨的索引,而source_code和tag欄位上沒有索引,從sql語句上看除了rtime上可能會過濾掉一些資料外,別的欄位基本就不會過濾掉資料,最開始看執行計劃的時候(執行計劃沒有保留下來),發現執行計劃裡走的是全表掃描,這個查詢1個月的資料,也有幾千萬,不走索引也算正常,但是我發現執行計劃裡同時出現partition range all,說明這個表是個分割槽表,於是詢問這下發現分割槽欄位是一個叫partition_id的這段,這個欄位是一個數字型別的,partition_id和rtime是存在一個對應關係的,假設資料是11月的資料,那麼這個partition_id的值就是11,而前面的查詢語句中並沒有出現partition_id,因此沒法用到分割槽裁剪,我不太明白當時設計的人為什麼用partition_id而不用查詢中常用的rtime做分割槽.針對前面的分析,說明這個語句其實只是要一個分割槽的資料,但是沒有分割槽條件,所以只能掃描整個表,而rtime和分割槽鍵之間存在一個對應關係,這時候有2個方法來解決:
1.在語句裡顯示指定分割槽(這個方法的缺點是如果查詢的資料跨分割槽那就不行)
2.對rtime做轉換,透過rtime得到partition_id,這部分就要改動程式,在執行這個sql之前,就要根據使用者輸入的時間算出partition_id.
原始的sql轉換成:
select
count(DISTINCT id),
       code
  FROM log
WHERE rtime >= to_date('20081101','yyyymmdd')
   AND rtime <= to_date('20081131','yyyymmdd')
   AND source_code = 7
   AND tag = '0' and partition_id=11
GROUP BY code  

這樣就能透過分割槽鍵來先做分割槽裁剪,效率上提升不少

具體的做法是這樣的:
根據輸入的時間首先得到一個符合這個條件的最小分割槽值,如輸入的是bewteen '2008-11-12' and '2008-12-20' ,那麼透過  '2008-11-12'轉換得到的是partition_id就是11,而透過'2008-12-20'轉換得到的就是partition_id=12,那麼語句裡就能加上partition_id between 11 and 12,這樣透過小小的改變就能使用上分割槽裁剪