表xxx 某字段 abc 类型是 VARCHAR2 (为方便后台不设置为datatime 类型的)
数据例如: 2011-01-24 14:37:07 2011-01-25 10:37:07 2011-01-26 11:37:07
如何用sql语句选取符合某段时间范围的数据呢?
比如大于 到 小于 在不改变字段类型的前提下··
数据例如: 2011-01-24 14:37:07 2011-01-25 10:37:07 2011-01-26 11:37:07
如何用sql语句选取符合某段时间范围的数据呢?
比如大于 到 小于 在不改变字段类型的前提下··
where date_column>='2011-01-10 00:00:00' and date_column<'2010-01-11 00:00:00';
--selelct * from xxx where to_date(abc,'yyyy-mm-dd hh24:mi:ss') between sysdate and sysdate+20
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')
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 行。
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,-- 我想请问:哪种方法效率高呢?
你换个函数索引试试:create index index_sdate2 ON music_searchlog(to_date(sdate2,'yyyy-mm-dd hh24:mi:ss'));
-- 但是如果你where 条件的左边老是滥用函数的话,该字段相应的索引不会被SQL执行计划采用,导致SQL执行计划不走索引(除非你在该字段创建基于函数的索引,我一般不创建基于函数的索引,除非迫不得已)-- 当然:还可以hint ,那是后话......