Question

oracle 优化时间查询

有一个海量量数据表，主键是ID和TIME，想查找一段时间内的全部数据并对其进行统计分析。在形如“select .....from 表 where 时间条件”的查询语句上，我想知道怎么写才好。比如我想查一个时间段内某个点的数据。有几种写法
1.where id=id编号 and to_char(time,'yyyy-mm-dd hh24:mi:ss')>time1 and to_char(time,'yyyy-mm-dd hh24:mi:ss')<time2;
2.where id=id编号 and time>to_date(time1,'yyyy-mm-dd hh24:mi:ss') and time>to_date(time2,'yyyy-mm-dd hh24:mi:ss');
我想知道：
1.哪种写法更加合理，更加快捷？
2.如果查询太慢又该如何优化？
3.为什么时间列用to_char()变成字符串后还可能进行大小的比较？

新手求指教。
请注意，每个点每个月有近8万条数据。表内有近两年的数据。数据量较大。

Answer 1

可定是第二种写法了啊。
首先对主键和附属建，建立联合索引。如果数据太大，有必要时可以建立分区。
to_char()变成字符串是对字符穿进行的比较。所以可以使用，但效率底下
time>to_date(time1,'yyyy-mm-dd hh24:mi:ss') 这个方法好的原因是：
数据库中存储的都是DATE类型，而你的参数可能是VARCHAR2,或DATE，但数据库是不可更改的。所以后面转换是对的。而在后面转换参数还不会影响索引的使用。如果你把索引字段进行了转换或处理，索引就失效。或者你建立特别的索引来引导。不然函数索引等等。

Answer 2

1. 理论上，第2种写法应该更快，
第1种写法有问题：
1). 第一种写法应该改成to_char(time,'yyyy-mm-dd')或trunc(time,'dd')，比如按日统计，不然字符串类型的数据比大小会有问题
2). 即便第一种写法改了，也会因为主键的UNIQUE INDEX是基于ID和TIME的，第一种写法将TIME转换成字符串类型，从而无法在查询中使用索引，导致全表扫描(FULL TABLE SCAN,FTS)
2. 如果查询速度慢，需要先看执行计划(比如PLSQL按F5)，最好是通过alter session set sql_trace=true，抓取实际运行时的执行计划，加以分析
3. to_char()后的字符串比较，有ORACLE的逻辑，比如哈稀或者通过转换成HEX编码比较大小
举例说明：

DECLARE
A VARCHAR2(20);
B VARCHAR2(20);
BEGIN
A:='AAA';
B:='BBB';
IF A>B THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('EXCEPTION OCCURED');
END;
＝＝＝
上述代码将A,B两个字符串变量赋值，并进行比较
能得到结果FALSE，而不是例外(EXCEPTION)

Answer 3

1.方法2是更加好的，方法1对字段使用函数，会使索引失效，并且会进行全表处理。2
2.另外 time字段的处理，建议使用between ... and ... id的这个条件，可以写在time字段条件的后面，尽量将刷选掉最多的放在最右边。
3.to_char之后是进行字符串比较的，由于，你的to_char完之后，字符串长度是一样的，所以不会有影响，但是如果对number类型操作的话，那么就不行了，例如：to_char之后 ‘999’>‘100000’

Answer 4

如果time列有索引的话，一般而言，方法2会快一些，因为方法1中对time进行了运算操作，导致oralce的执行计划不会采用time列的索引。
字符串是可以比较大小的，2比1大，2012比2011大，23:00:00比22:59:59大，time1和time2这两个字串，其格式想必也是和to_char(time)的格式一样的，都是xxxx-xx-xx xx:xx:xx，自然可以比大小，而且大的那个肯定在时间上也是大的。

Answer 5

（1）人觉得用to_date更加合理，处理是，只要做一个变量的转换，第一种写法，数据库中涉及记录都要转换；
（2）查询慢在时间上也增加索引；

（3）to_char变字符串，字符串也有大小的；

追问

time作为双主键之一不是已经默认有索引了吗？如果建立基于函数的索引
CREATE INDEX Ind_time ON 表 (to_char(time,'YYYY-MM-DD hh24:mi:ss'))会不会好点？

追答

time作为双主键之一已经默认有索引，不要再建了，可以自己动手试下，看看哪种情况下速度快。表不是很庞大的话，两种都没有关系，能运行就OK了。