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

via: http://my.oschina.net/baishi/blog/293742

-- 查询一表中当天生成的数据

-- 原表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);

已用时间: 00: 00: 16.32

| 0 | SELECT STATEMENT | | 1 | 8 | 12580 (22)| 00:02:31 |

|* 2 | INDEX FAST FULL SCAN| MOBILEFRENDS_CDATE_IDX | 129K| 1012K| 12580 (22)| 00:02:31 |

2 - filter(TRUNC(INTERNAL_FUNCTION("CDATE"))=TRUNC(SYSDATE@!-1))

0 recursive calls

-- 方法三：不用函数

hll@SZTYORA> select count(*) from mobilefrends where cdate>=trunc(sysdate) and cdate<trunc(sysdate)+1;

已用时间: 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 |

2 - filter(TRUNC(SYSDATE@!-1)<TRUNC(SYSDATE@!))

3 - access("CDATE">=TRUNC(SYSDATE@!-1) AND "CDATE"<TRUNC(SYSDATE@!))

221 consistent gets

-- 由上三种方法可见：

-- 方法一：用to_char()函数，耗时17.18秒，逻辑读为36896次，执行的是全索引扫描；

-- 方法二：用trunc()函数，耗时16.32秒，逻辑读也是36896次，执行的也是全索引扫描；

-- 方法三：不用函数，耗时0.43秒，逻辑读为221次，执行的是索引范围扫描；