Oracle_查询当天记录 三种方法效率比较
来源:互联网 发布:作品集目录设计 知乎 编辑:程序博客网 时间:2024/05/13 07:12
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);
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;
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次,执行的是索引范围扫描;
- Oracle_查询当天记录 三种方法效率比较
- JDBC获取SQL查询语句返回记录数的三种方式效率比较
- JDBC获取SQL查询语句返回记录数的三种方式效率比较
- sql查询当天记录
- SQL Server 查询当天记录
- SQL Server 查询当天记录
- sql查询当天的记录
- j2me三种图片切割方法之效率比较
- 三种字符串连接方法的效率比较
- oracle查询当天数据的效率问题
- Sql 查询当天、本周、本月记录
- SQL 查询当天,本月,本周的记录
- sql 查询 当天 当月 上月 记录
- SQL 查询当天,本月,本周的记录
- Sql 查询当天、本周、本月记录
- Sql 查询当天、本周、本月记录
- SQL 查询当天,本月,本周的记录
- Sql 查询当天、本周、本月记录
- 在活体检测的程序上把检测到的活体显示在右上角
- ActiveMQ_页面上显示队列的信息
- linux基础练习(七,八)
- producer and consumer
- Oracle_画图解释 SQL join 语句
- Oracle_查询当天记录 三种方法效率比较
- 四则运算
- 【问题解决】BootStrap.css与layDate日期选择样式起冲突的解决办法
- Java_assertion
- LabVIEW中的While循环应用示例
- Test_写给精明Java开发者的测试技巧
- 很详细的解答tree of tree 树状DP(有图)
- java中的命名--类型转换--运算符--流程控制语句等
- 类的加载和初始化总结