最近改一个BUG,需要根据时间区间进行查询,但是反应查询时间特别长,
select *
from A t, B mbf, C m
where t.firmId = mbf.firmId
and mbf.brokerId = m.brokerId
and trunc(cleardate ) <= to_Date('20120312','yyyymmdd')
and trunc(cleardate ) >= to_Date('20110101','yyyymmdd')
数据量
select count(*) from A --105824591
select count(*) from B --19993
select count(*) from C --650
select *
from A t, B mbf, C m
where t.firmId = mbf.firmId
and mbf.brokerId = m.brokerId
and cleardate <= to_Date('20120312','yyyymmdd')
and cleardate >= to_Date('20110101','yyyymmdd'
带TRUNC执行时间稳定在12S,不带TRunc执行时间稳定在1-2S
总结:对于历史表的归档,如果不是对时间要求很严格,可以将时间设置成年月日的形式,提高查询效率。