ITPub博客

对应用中一个sql语句的调整——时间字段

原创 Linux操作系统 作者:tigersun 时间:2007-11-13 17:27:54 0 删除 编辑
应用中有下列sql语句,本身没有问题,就是磁盘读取颇高,
查看执行计划,两表均为TABLE ACCESS FULL

注:
    学生选课表250万行,学生信息表60万行,count()结果是2万行。
    注册时间和开始时间字段均有b-tree索引,状态和标记字段有bitmap索引。


select count(distinct b.学生帐号id ) num
from 学生选课表 b, 学生信息表 a  
where  a.帐号id =b.学生帐号id   
and a.注册时间 >=to_date('2006-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')   
and a.注册时间 <=to_date('2007-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss')   
and b.开始时间 >=to_date('2006-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')   
and b.开始时间 <=to_date('2007-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss')   
and  b.状态='正学习'
and b.标记=0

执行计划
----------------------------------------------------------
Plan hash value: 1699183140

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    31 |  6529   (2)| 00:01:19 |
|   1 |  SORT GROUP BY      |       |     1 |    31 |            |          |
|*  2 |   HASH JOIN         |       | 19392 |   587K|  6529   (2)| 00:01:19 |
|*  3 |    TABLE ACCESS FULL| 学生选| 19392 |   359K|  3857   (3)| 00:00:47 |
|*  4 |    TABLE ACCESS FULL| 学生信| 96900 |  1135K|  2671   (2)| 00:00:33 |
-----------------------------------------------------------------------------


最初以为是distinct的问题,改成如下结构,发现执行计划没有任何改变:

select   count(a.帐号id) num
from 学生信息表 a   
where exists( select 'X'
              from 学生选课表 b
              where b.学生帐号id = a.帐号id and
              b.开始时间 >=to_date('2005-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
              and b.开始时间 <=to_date('2007-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
              and b.标记=0
              and b.状态='正学习'
             )
and a.注册时间 >=to_date('2006-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.注册时间 <=to_date('2007-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss');

采用hint提示强制使用索引,才发现oracle的执拗不无道理,效果更差:

select /*+ INDEX(b STUDY_DATA_INDEX)*/ count(distinct b.学生帐号id ) num
from 学生选课表 b, 学生信息表 a  
where  a.帐号id =b.学生帐号id   
and a.注册时间 >=to_date('2006-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')   
and a.注册时间 <=to_date('2007-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss')   
and b.开始时间 >=to_date('2006-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')   
and b.开始时间 <=to_date('2007-10-31 23:59:59','yyyy-mm-dd hh24:mi:ss')   
and  b.状态='正学习'
and b.标记=0

执行计划
----------------------------------------------------------
Plan hash value: 3024041955

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     1 |    31 | 10318   (1)| 00:02:04 |
|   1 |  SORT GROUP BY                |                  |     1 |    31 |            |          |
|*  2 |   HASH JOIN                   |                  | 19392 |   587K| 10318   (1)| 00:02:04 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| 学生选课表       | 19392 |   359K|  7645   (1)| 00:01:32 |
|*  4 |     INDEX RANGE SCAN          | STUDY_DATA_INDEX |   387K|       |  2296   (1)| 00:00:28 |
|*  5 |    TABLE ACCESS FULL          | 学生信息表       | 96900 |  1135K|  2671   (2)| 00:00:33 |
--------------------------------------------------------------------------------------------------



经过验证,这条语句也不是不能使用索引,而是只有当count()的结果小于一定比例的时候,该语句才会用到索引

但语句由应用而定,磁盘读取过高的问题不能不解决,因为它不仅关系到自己,还会影响到其它语句的执行效率,于是采用了一个也许不太合适的方法:

在注册时间和开始时间字段建立基于函数的索引
to_char(开始时间)
to_char(注册时间)
然后修改sql语句并查看执行计划:
select   count(a.帐号id) num
from 学生信息表 a   
where exists( select 'X'
              from 学生选课表 b
              where b.学生帐号id = a.帐号id
              and to_char(b.开始时间,'yyyy-mm-dd hh24:mi:ss') >='2006-11-01 00:00:00'
              and to_char(b.开始时间,'yyyy-mm-dd hh24:mi:ss') <='2007-10-31 23:59:59'
              and b.标记=0
              and b.状态='正学习'
             )
and to_char(a.注册时间,'yyyy-mm-dd hh24:mi:ss') >= '2006-11-01 00:00:00'
and to_char(a.注册时间,'yyyy-mm-dd hh24:mi:ss') <= '2007-10-31 23:59:59';
执行计划
----------------------------------------------------------
Plan hash value: 3821826017

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     1 |    31 |   473   (1)| 00:00:06 |
|   1 |  SORT AGGREGATE                     |                         |     1 |    31 |            |          |
|   2 |   NESTED LOOPS                      |                         |   314 |  9734 |   473   (1)| 00:00:06 |
|   3 |    SORT UNIQUE                      |                         |   314 |  5966 |   158   (2)| 00:00:02 |
|   4 |     TABLE ACCESS BY INDEX ROWID     | 学生选课表              |   314 |  5966 |   158   (2)| 00:00:02 |
|   5 |      BITMAP CONVERSION TO ROWIDS    |                         |       |       |            |          |
|   6 |       BITMAP AND                    |                         |       |       |            |          |
|*  7 |        BITMAP INDEX SINGLE VALUE    | BMPIND_MARK             |       |       |            |          |
|*  8 |        BITMAP INDEX SINGLE VALUE    | BMPIND_STUCOURSE_STATUS |       |       |            |          |
|   9 |        BITMAP CONVERSION FROM ROWIDS|                         |       |       |            |          |
|  10 |         SORT ORDER BY               |                         |       |       |            |          |
|* 11 |          INDEX RANGE SCAN           | FBI_COURSE_BEGIN_TIME   | 11296 |       |    51   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID      | 学生信息表              |     1 |    12 |     2   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN               | SYS_C00147216           |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

总结: 通常我们更愿意采用 to_date()函数来解决时间字段的问题,这里采用to_char(),至少从执行计划上看,对该sql的性能应该是有所提升(请高手验证,我看执行计划也是一知半解),但这种方式究竟是否合适,还请前辈不吝指教。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/310745/viewspace-6320/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-26

  • 博文量
    31
  • 访问量
    22666