sql 时间范围的问题

Question

  表xxx 某字段 abc 类型是 VARCHAR2 （为方便后台不设置为datatime 类型的）
  数据例如： 2011-01-24 14:37:07    2011-01-25 10:37:07  2011-01-26 11:37:07    
  如何用sql语句选取符合某段时间范围的数据呢？
  
  比如大于 到 小于 在不改变字段类型的前提下··

Answer 1

-- 日期格式是否统一，如：YYYY-MM-DD HH24:MI:SS-- 如果统一的话，应该直接用 >= and <= 就OK啦，如：
where date_column>='2011-01-10 00:00:00' and date_column<'2010-01-11 00:00:00';

Answer 2

--查询时转换成date型的就好了
--selelct * from xxx where to_date(abc,'yyyy-mm-dd hh24:mi:ss') between sysdate and sysdate+20

Answer 3

TO_DATE('abc','YYYY-MM-DD HH24:MI:SS')
BETWEEN TO_DATE('2011-01-25 10:37:07','YYYY-MM-DD HH24:MI:SS') AND 
TO_DATE('2011-01-26 11:37:07','YYYY-MM-DD HH24:MI:SS')

Answer 4

-- 测试：
music@SZTYORA> desc music_searchlog
 名称                                                                                      是否为空? 类型
 ----------------------------------------------------------------------------------------- -------- ---------------------------
 PREKEY                                                                                             VARCHAR2(800)
 SEARCHKEY                                                                                          VARCHAR2(800)
 SEARCH_STATUS                                                                                      NUMBER(4)
 SDATE                                                                                     NOT NULL DATE
 SUCCESSFUL_RATE                                                                                    NUMBER(5,2)
 MOBILE                                                                                             VARCHAR2(20)music@SZTYORA> alter table music_searchlog add sdate2 varchar2(20);表已更改。music@SZTYORA> update music_searchlog set sdate2=to_char(sdate,'yyyy-mm-dd hh24:mi:ss');已更新2205452行。music@SZTYORA> commit;提交完成。music@SZTYORA> select count(*) from music_searchlog
  2  where sdate>=to_date('2011-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')
  3    and sdate<to_date('2011-01-11 00:00:00','yyyy-mm-dd hh24:mi:ss');  COUNT(*)
----------
     33692已选择 1 行。music@SZTYORA> select count(*) from music_searchlog
  2  where sdate2>='2011-01-10 00:00:00' and sdate2<'2010-01-11 00:00:00';  COUNT(*)
----------
         0已选择 1 行。music@SZTYORA> select count(*) from music_searchlog
  2  where sdate2>='2011-01-10 00:00:00' and sdate2<'2011-01-11 00:00:00';  COUNT(*)
----------
     33692已选择 1 行。

Answer 5

music@SZTYORA> create index music_searchlog_inx2 on music_searchlog(sdate2);索引已创建。music@SZTYORA> set autotrace traceonly;
music@SZTYORA> set timing on;
music@SZTYORA> select count(*) from music_searchlog
  2   where to_date(sdate2,'yyyy-mm-dd hh24:mi:ss')>=to_date('2011-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')
  3     and to_date(sdate2,'yyyy-mm-dd hh24:mi:ss')<to_date('2011-01-11 00:00:00','yyyy-mm-dd hh24:mi:ss');已选择 1 行。已用时间:  00: 00: 05.79执行计划
----------------------------------------------------------
Plan hash value: 3428832509--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     1 |    12 |  5956   (5)| 00:01:12 |
|   1 |  SORT AGGREGATE    |                 |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS FULL| MUSIC_SEARCHLOG | 34099 |   399K|  5956   (5)| 00:01:12 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter(TO_DATE("SDATE2",'yyyy-mm-dd hh24:mi:ss')>=TO_DATE(' 2011-01-10
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE("SDATE2",'yyyy-mm-dd
              hh24:mi:ss')<TO_DATE(' 2011-01-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      20402  consistent gets
          0  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedmusic@SZTYORA> select count(*) from music_searchlog
  2    where sdate2>='2011-01-10 00:00:00' and sdate2<'2011-01-11 00:00:00';已选择 1 行。已用时间:  00: 00: 00.25执行计划
----------------------------------------------------------
Plan hash value: 1682168774------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |    12 |   136   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE   |                      |     1 |    12 |            |          |
|*  2 |   INDEX RANGE SCAN| MUSIC_SEARCHLOG_INX2 | 34099 |   399K|   136   (1)| 00:00:02 |
------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("SDATE2">='2011-01-10 00:00:00' AND "SDATE2"<'2011-01-11 00:00:00')Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        250  consistent gets
        148  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
-- 看吧：这就是在字段上随便用函数的效果：可以说效率是如此之差，你能忍受吗？
-- 从上面我们可以看到：第一个SQL语句：
select count(*) from music_searchlog
   where to_date(sdate2,'yyyy-mm-dd hh24:mi:ss')>=to_date('2011-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')
     and to_date(sdate2,'yyyy-mm-dd hh24:mi:ss')<to_date('2011-01-11 00:00:00','yyyy-mm-dd hh24:mi:ss');-- 是全表扫描，执行时间为5.79秒，产生逻辑读次数为20402次，
-- 而第二个SQL语句：
select count(*) from music_searchlog
  where sdate2>='2011-01-10 00:00:00' and sdate2<'2011-01-11 00:00:00';-- 执行时间仅0.25秒，产生逻辑读次数仅为250，-- 我想请问：哪种方法效率高呢？

Answer 6

罗哥，谢谢你给我指出，但是我没明白你的什么意思，to_date()转换是可以的啊，为什么不行，你对sdate2字段加了 index ，to_date()后肯定全表扫描啊
你换个函数索引试试：create index index_sdate2 ON music_searchlog(to_date(sdate2,'yyyy-mm-dd hh24:mi:ss'));

Answer 7

哦对了，也许你说的是类型转换耗费的COST吧，这个之前好像你专门有个帖子来说类型转换耗费COST的吧  我记得我还给你测试过

Answer 8

-- 尽量保持字段的原型，当字段创建索引时，就会自动使用该索引以提高效率!
-- 但是如果你where 条件的左边老是滥用函数的话，该字段相应的索引不会被SQL执行计划采用，导致SQL执行计划不走索引(除非你在该字段创建基于函数的索引，我一般不创建基于函数的索引，除非迫不得已)-- 当然：还可以hint ，那是后话......