-- 查询一表中当天生成的数据 -- 原表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,‘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); 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) and cdate=TRUNC(SYSDATE@!-1) AND "CDATE"
oracle 查询当天记录 三种方法效率比较,,
oracle 查询当天记录 三种方法效率比较