在test表里有一个dadate日期字段里面的格式如下
id dadate
1 2011-3-23 10:23:45
2 2011-2-21 12:34:21
3 2011-3-12 6:23:12
我想按dadate 查询当天的记录如何查询?望高手告知,谢谢
id dadate
1 2011-3-23 10:23:45
2 2011-2-21 12:34:21
3 2011-3-12 6:23:12
我想按dadate 查询当天的记录如何查询?望高手告知,谢谢
解决方案 »
- EPM@ERP
- IMP-00041: 警告: 创建的对象带有编译警告....帮忙看一看,网上找不到解
- Oralce dba 要具备那些素质?
- 在线等!!!!!急!!!!!!!!!!
- ORACLE如何批量插入数据?
- 关于二个数据库的数据更新问题,请大家给点意见!
- oralce sql串里赋变量值
- 请告诉我, 我已有一个用来针对sqlserver数据库创建的sql脚本文件,如何快速地得到针对oracle的sql脚本文件??
- 新手to_date() 函数的疑惑~
- 用orcle搭建一个客户服务器平台,该如何搭建?(通过vc的应用程序在客户端访问)--都有分给的.
- 请教如何在不用循环的情况使用SELECT实现效果
- 请教一个SQL查询语句写法。。
-- 原表mobilefrends中的cdate字段上有索引,创建索引语句是:create index mobilefrends_cdate_idx on mobilefrends(cdate);---------------------------------------------------------------------------------------------------------------------
-- 方法一:用to_char()函数hll@SZTYORA> select count(*) from mobilefrends where to_char(cdate,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd'); COUNT(*)
----------
82119已用时间: 00: 00: 17.18执行计划
----------------------------------------------------------
Plan hash value: 3731074549------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 11773 (16)| 00:02:22 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 129K| 1012K| 11773 (16)| 00:02:22 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(TO_CHAR(INTERNAL_FUNCTION("CDATE"),'yyyy-mm-dd')=TO_CHAR(SYSDATE@!-1,'yyyy
-mm-dd'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
36896 consistent gets
0 physical reads
0 redo size
345 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---------------------------------------------------------------------------------------------------------------------
-- 方法二:用trunc()函数hll@SZTYORA> select count(*) from mobilefrends where trunc(cdate)=trunc(sysdate-1); COUNT(*)
----------
82119已用时间: 00: 00: 16.32执行计划
----------------------------------------------------------
Plan hash value: 3731074549------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 12580 (22)| 00:02:31 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 129K| 1012K| 12580 (22)| 00:02:31 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
36896 consistent gets
0 physical reads
0 redo size
345 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---------------------------------------------------------------------------------------------------------------------
-- 方法三:不用函数
hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate-1) and cdate<trunc(sysdate); COUNT(*)
----------
82119已用时间: 00: 00: 00.43执行计划
----------------------------------------------------------
Plan hash value: 2668176725---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| MOBILEFRENDS_CDATE_IDX | 48 | 384 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))
3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
221 consistent gets
0 physical reads
0 redo size
345 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---------------------------------------------------------------------------------------------------------------------
-- 由上三种方法可见:
-- 方法一:用to_char()函数,耗时17.18秒,逻辑读为36896次,执行的是全索引扫描;
-- 方法二:用trunc()函数,耗时16.32秒,逻辑读也是36896次,执行的也是全索引扫描;
-- 方法三:不用函数,耗时0.43秒,逻辑读为221次,执行的是索引范围扫描;
-- 请问:哪种方法好呢?
select * from test where trunc(dadate)=trunc(sysdate)