应用中有下列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博客 ” ，链接：https://blog.itpub.net/310745/viewspace-6320/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
请教一个sql的优化
下一篇：
请教缓存命中率的问题
请登录后发表评论
登录
全部评论
