今天翻了下和朋友的聊天记录,看到以前优化过的一条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,这样通过小小的改变就能使用上分区裁剪