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

Myth丶恋晨 2022-05-28 10:40 181阅读 0赞

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

数据库技术学习 [https://www.itkc8.com][https_www.itkc8.com]

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

\------------------------------------------------------------------------------------------------

\------------------------------------------------------------------------------------------------

|   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

\------------------------------------------------------------------------------------------------

\------------------------------------------------------------------------------------------------

|   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

\---------------------------------------------------------------------------------------------

\---------------------------------------------------------------------------------------------

|   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次，执行的是索引范围扫描；

数据库技术学习 [https://www.itkc8.com][https_www.itkc8.com]

[https_www.itkc8.com]: https://www.itkc8.com/

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle SQL查询效率低下：优化方法

相关 oracle查询日期为当天的数据sql

相关 mybatis的三种批量插入以及次效率比较

相关 oracle三种复制技术之比较

相关 java的三种写文件方法的效率性能比较

相关提高SQL查询效率的30种方法

相关 Oracle_查询当天记录三种方法效率比较

相关 Oracle提高查询效率方法总结

相关 ORACLE批量更新四种方法比较

相关 java的三种写文件方法的效率性能比较