where子句中带日期字段的查询优化

Question

优化查询的简单方法是为where子句中存在的字段加索引,但考虑如下情况:
我有一张表,有一个日期记录代表每行插入时的日期加时间,当表里有上百万条记录时,查询某个时间段
的记录时,会很慢.
表结构:id    content       mydate
1     'test'        2005-9-30 12:00:00
2     'test2'       2005-9-30 12:00:01查询语句如下:select * from t_mytable where mydate>=v_datefrom and mydate<v_dateto;v_datefrom和v_dateto是时间段的开始和结束时间点.这样的查询语句如何优化?因为表里有上百万条的记录,而查询时使用的是>=和<这种比较,所以为mydate加了索引也必须导致全表遍历啊.如何解决啊?这种查询应该是经常遇到的查询啊.望大哥大姐给个答复.

Answer 1

查询9月份的全部数据
select * from tb_name 
where substr(to_date(mydate,'yyyymmdd'))='200509';

Answer 2

更正：
查询9月份的全部数据
select * from tb_name 
where substr(to_char(mydate,'yyyymmdd'))='200509';

Answer 3

气死了再更正：查询9月份的全部数据
select * from tb_name 
where substr(to_char(mydate,'yyyymmdd'),1,6)='200509';

Answer 4

"查询时使用的是>=和<这种比较,所以为mydate加了索引也必须导致全表遍历啊."？你试过吗？按mydate建立索引后，执行
select * from t_mytable where mydate>=v_datefrom and mydate<v_dateto;
是应该能用上索引的。除非oracle认为读取的记录与表总记录数相比比较多，才会采用表扫描。这是如果实际返回的记录数不多，可以考虑对表做分析。

Answer 5

我试过了,加了索引比不加索引反而慢,原因我分析可能有如下两点:
1.因为索引是一些hash值而已,因而只能适用于=和like这种场合,而这个sql语句使用的是>=和<,所以此时索引并不起作用.
2.由于每条记录的date字段的值都是不一样的,所以加了索引以后,索引占用的内存很大,超出了PGA中内存值的设置,导致如下结果:即使索引可以用,也要用到内存磁盘交换,导致变慢.我说的这两条有没有道理啊?给解个惑吧...

Answer 6

>=和<应该是用的上索引的；数据量比较大，用簇索引吧