oracle查询当天记录三种方法效率比较

-- 查询一表中当天生成的数据
-- 原表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"