一张表A,字段有id...rq,其中rq是date字段,做个简单的查询:select* from A where id = '9' and rq between Date'2010-06-01' and Date '2010-06-19',查询速度很慢,需要1.7秒,重复执行也是如此,如果简单的把前一个'2010-06-01'改成'2009-01-01',立即速度飞快,只要0.03秒,同样的查询,日期段拉大反倒查询速度猛升,不知道究竟是什么原因,有没有大大遇到过此类问题的,望指点迷津啊
调试欢乐多
看看两个sql的执行计划是否相同?会不会走了不同的路线?
--试试这样看看执行计划
select * from A
where id = '9' and rq >=Date'2010-06-01' and rq<=Date '2010-06-19'
sql 如下:
select sum(sj_je) as zj from a, b where
a.pz_xh = b.pz_xh and a.yzmx_xh = b.yzmx_xh and a.swglm in (select swglm from
c where glqy_dm = '3209243903') and a.zf_bj = 0 and a.sf_bj = '0' and b.sb_rq
between Date '2010-01-01' and Date '2010-06-11' and rk_rq is not null
//选择日期离得远的,速度快的查询,用的是nested loop,先走的是T_DJ_JGNSR,然后T_ZS_JKMX,T_ZS_YZMX
SELECT STATEMENT, GOAL = CHOOSE 22705 1 149 CHOOSE
SORT AGGREGATE 1 149
NESTED LOOPS 22705 2 298
NESTED LOOPS 22378 109 10137
TABLE ACCESS BY GLOBAL INDEX ROWID DB_DJGL T_DJ_JGNSR 469 67 1407 6 ANALYZED
INDEX RANGE SCAN DB_DJGL I_DJ_JGNSR_GLQY 7 1328 ANALYZED 1
PARTITION RANGE ALL
TABLE ACCESS BY LOCAL INDEX ROWID DB_SBZS T_ZS_JKMX 327 2 144 5 ANALYZED
INDEX RANGE SCAN DB_SBZS I_ZS_JKMX_SWGLM 31 129 ANALYZED 1
TABLE ACCESS BY GLOBAL INDEX ROWID DB_SBZS T_ZS_YZMX 3 1280912 71731072 4 ANALYZED
INDEX UNIQUE SCAN DB_SBZS PK_T_ZS_YZMX 2 53 ANALYZED 2 //选择日期靠近的,速度慢的查询,用的是hash join,先走的是T_ZS_JKMX,然后T_DJ_JGNSR,T_ZS_YZMX
SELECT STATEMENT, GOAL = CHOOSE 22482 1 149 CHOOSE
SORT AGGREGATE 1 149
HASH JOIN 22482 1 149
TABLE ACCESS BY LOCAL INDEX ROWID DB_SBZS T_ZS_JKMX 327 1 72 5 ANALYZED
NESTED LOOPS 22378 109 10137
TABLE ACCESS BY GLOBAL INDEX ROWID DB_DJGL T_DJ_JGNSR 469 67 1407 6 ANALYZED
INDEX RANGE SCAN DB_DJGL I_DJ_JGNSR_GLQY 7 1328 ANALYZED 1
PARTITION RANGE ALL
INDEX RANGE SCAN DB_SBZS I_ZS_JKMX_SWGLM 31 129 ANALYZED 1
PARTITION RANGE ALL
TABLE ACCESS BY LOCAL INDEX ROWID DB_SBZS T_ZS_YZMX 103 675 37800 4 ANALYZED
INDEX RANGE SCAN DB_SBZS I_ZS_YZMX_SBRQ 31 101226 ANALYZED 2可以看到,速度慢的查询虽然会用到I_ZS_YZMX_SBRQ这个日期索引,但是我的表是T_DJ_JGNSR最小,只有2万多,其他两个都是缴款明细,百万级的表。所以反倒走nested loop最快,现在问题来了,怎么样强制上面的sql查询走nested loops