DATE类型截取到天的效率

最新推荐文章于 2022-07-29 14:20:22 发布
最新推荐文章于 2022-07-29 14:20:22 发布
阅读量187 收藏
点赞数
文章标签： 数据库

在ITPUB上看了一个帖子，根据日期类型对每天的记录进行GROUP BY，帖子的地址如下：http://www.itpub.net/thread-1564295-1-1.html

 

 

这种包含全表扫描执行GROUP BY的语句是否还有优化的余地吗，事实上确实还有，因为对于处理日期类型，TO_CHAR并没有TRUNC高效。

下面看一个简单的例子：

SQL> CREATE TABLE T_DATE AS
  2  SELECT ROWNUM ID, CREATED
  3  FROM DBA_OBJECTS A, (SELECT 1 FROM DUAL CONNECT BY ROWNUM < 100) 
  4  WHERE ROWNUM <= 1000000;

Table created.

SQL> SELECT COUNT(*) FROM T_DATE;

  COUNT(*)
----------
   1000000

SQL> SET TIMING ON
SQL> SELECT TO_CHAR(CREATED, 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TO_CHAR(CREATED, 'YYYY-MM-DD');

TO_CHAR(CR   COUNT(*)
---------- ----------
2012-01-07       3600
2012-01-08       3750
2012-01-09       4650
2012-01-06     987925
2012-01-10         75

Elapsed: 00:00:00.46
SQL> SELECT TO_CHAR(CREATED, 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TO_CHAR(CREATED, 'YYYY-MM-DD');

TO_CHAR(CR   COUNT(*)
---------- ----------
2012-01-07       3600
2012-01-08       3750
2012-01-09       4650
2012-01-06     987925
2012-01-10         75

Elapsed: 00:00:00.40
SQL> SELECT TO_CHAR(CREATED, 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TO_CHAR(CREATED, 'YYYY-MM-DD');

TO_CHAR(CR   COUNT(*)
---------- ----------
2012-01-07       3600
2012-01-08       3750
2012-01-09       4650
2012-01-06     987925
2012-01-10         75

Elapsed: 00:00:00.39
SQL> SELECT TO_CHAR(CREATED, 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TO_CHAR(CREATED, 'YYYY-MM-DD');

TO_CHAR(CR   COUNT(*)
---------- ----------
2012-01-07       3600
2012-01-08       3750
2012-01-09       4650
2012-01-06     987925
2012-01-10         75

Elapsed: 00:00:00.44
SQL> SELECT TO_CHAR(TRUNC(CREATED), 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TRUNC(CREATED);

TO_CHAR(TR   COUNT(*)
---------- ----------
2012-01-06     987925
2012-01-10         75
2012-01-08       3750
2012-01-07       3600
2012-01-09       4650

Elapsed: 00:00:00.36
SQL> SELECT TO_CHAR(TRUNC(CREATED), 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TRUNC(CREATED);

TO_CHAR(TR   COUNT(*)
---------- ----------
2012-01-10         75
2012-01-07       3600
2012-01-09       4650
2012-01-06     987925
2012-01-08       3750

Elapsed: 00:00:00.35
SQL> SELECT TO_CHAR(TRUNC(CREATED), 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TRUNC(CREATED);

TO_CHAR(TR   COUNT(*)
---------- ----------
2012-01-10         75
2012-01-07       3600
2012-01-09       4650
2012-01-06     987925
2012-01-08       3750

Elapsed: 00:00:00.36
SQL> SELECT TO_CHAR(TRUNC(CREATED), 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TRUNC(CREATED);

TO_CHAR(TR   COUNT(*)
---------- ----------
2012-01-10         75
2012-01-07       3600
2012-01-09       4650
2012-01-06     987925
2012-01-08       3750

Elapsed: 00:00:00.34

如果仅从执行计划和逻辑读上进行分析，两个SQL没有任何区别：

SQL> set autot on
SQL> SELECT TO_CHAR(CREATED, 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TO_CHAR(CREATED, 'YYYY-MM-DD');

TO_CHAR(CR   COUNT(*)
---------- ----------
2012-01-07       3600
2012-01-08       3750
2012-01-09       4650
2012-01-06     987925
2012-01-10         75

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
Plan hash value: 534547868

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  1294K|    11M|   726   (6)| 00:00:09 |
|   1 |  HASH GROUP BY     |        |  1294K|    11M|   726   (6)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| T_DATE |  1294K|    11M|   694   (1)| 00:00:09 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2490  consistent gets
       2487  physical reads
          0  redo size
        754  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> SELECT TO_CHAR(TRUNC(CREATED), 'YYYY-MM-DD'), COUNT(*)
  2  FROM T_DATE
  3  GROUP BY TRUNC(CREATED);

TO_CHAR(TR   COUNT(*)
---------- ----------
2012-01-10         75
2012-01-07       3600
2012-01-09       4650
2012-01-06     987925
2012-01-08       3750

Elapsed: 00:00:00.34

Execution Plan
----------------------------------------------------------
Plan hash value: 534547868

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  1294K|    11M|   726   (6)| 00:00:09 |
|   1 |  HASH GROUP BY     |        |  1294K|    11M|   726   (6)| 00:00:09 |
|   2 |   TABLE ACCESS FULL| T_DATE |  1294K|    11M|   694   (1)| 00:00:09 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2490  consistent gets
       2487  physical reads
          0  redo size
        761  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

但是观察两个SQL的平均执行时间，会发现使用TRUNC方式比TO_CHAR有1/8的性能提升，对于执行计划完全相同的情况而言，这个比率已经很高了。

其实导致问题的原因在于DATE类型的存储，DATE由7个字节组成，分别为世纪、年、月、日、时、分、秒。对于TRUNC函数而言，只是简单的舍弃掉后面三个字节，因此效率最高，而TO_CHAR需要将内部的存储格式转化为字符格式，显然会消耗更多的资源。

两个SQL返回结果顺序的不同也说明了这一点，TRUNC函数进行HASH GROUP的是日期格式，而TO_CHAR函数进行HASH GROUP的是字符类型，导致了最终结果返回顺序的差异性。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-714647/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-714647/

ckawt40802
关注
获取Date某一天
......
01-29 1049
1. 获取当前北京时间 let dt = new Date(); 2. 获取当月某一天 let dt = new Date(); dt.setDate(n) // n为某日， 取值范围整数，负无穷到正无穷 注意： n 在当前月的范围内，获取的是当前月的第 n 天；n 超出当前月，则获取的是下个月或者下下个月......的某天；n为负整数时，获取的是上个月或者上上个月......的某天 3. ...
java 验证时间格式 效率_有关Java中日期格式化的效率实录
weixin_32296949的博客
02-13 376
java中格式化日期为固定格式的字符串，无外乎有两种方法：DateFormat 和String.format(JDK1.5+)但是哪个方法效率更高呢？本人做了一个实验，发现了一个很有趣的现象。当系统第一次使用SimpleDateFormat进行格式化日期对象的时候，消耗资源是比较大的。而第一次使用之后，每次再使用该对象甚至是该类不同的对象的同一个方法时，又会极大的节省时间。而String.form...
mysql date_format 效率_Mysql中谓词使用date_format的优化
weixin_36377516的博客
01-19 861
+----+-------------+---------------------+-------+--------------------------------------+------------------+---------+----------------------+------+----------------------------------------------------...
谈一下函数DATE_FORMAT的性能问题
weixin_45782664的博客
12-09 4686
最近在做一个功能,里面sql中用到了DATE_FORMAT的这个转换时间的函数,刚开始用觉得挺好,数据量少也ok,当数据量不断增长到一定程度发现了问题以下是sql运行结果在一个两全万条的数据库中查询(数据库做了分区) 发现时间长达103秒多. 在看一下不用这个函数的情况 不到1秒,基本秒出,差距之大… 结论: 我们在log_time上创建索引,而DATE_FORMAT(log_time,’%Y-%...
MySql用DATE_FORMAT截取DateTime字段的日期值
09-10
标题和描述提到的问题是关于如何使用`DATE_FORMAT()`来截取`DateTime`字段的日期值，下面我们将详细探讨这一操作。 首先，`DateTime`类型在MySQL中是一种混合日期和时间的类型，它可以存储精确到微秒的日期和时间...
Oracle Date常用方法封装
07-28
在Oracle数据库中，Date类型是用于存储日期和时间值的关键数据类型。Oracle Date常用方法封装通常涉及对日期的计算、格式化以及与时间戳的转换等操作。在Oracle环境中，我们可以通过PL/SQL来实现这些功能，将常用的...
object转化为时间类型
05-15
### 在C#中将Object转换为时间类型...通过上述分析，我们可以了解到在C#中如何有效地将`object`类型的变量转换为`DateTime`类型，并且能够理解和掌握相关的代码实现细节。这对于提高程序的健壮性和效率都具有重要意义。
javascript中Date format(js日期格式化)方法小结.docx
01-13
这些技巧可以帮助开发者快速高效地完成日期时间的格式化工作，提高开发效率。 #### 方法一：扩展 Date 原型 这种方法通过向 `Date` 原型添加一个 `Format` 方法来实现日期格式化。该方法支持以下格式符号： - `y`:...
date0425111111111111111111111
最新发布
04-25
在Java中，处理字符串可以用到`String`类的各种方法，比如`substring()`用于截取子串，`indexOf()`用于查找子串，`replace()`用于替换字符，以及`split()`用于字符串分割等。 【标签】"java" 指定了讨论的主题是...
MYSQL sql语句针对数据记录时间范围查询的效率对比
binbin3828的博客
05-22 1万+
MYsql 针对where条件时间范围的查询效率方式对比 end_rec表，游戏记录表 时间字段 time_str 表示每局游戏结束时间，已经针对 time_str加了 B-tree 索引，一共68w条数据 需求： 查询今日的组局数量 1，方式一，用 TO_DAYS() 函数 select count(*) from end_rec where TO_DAYS(time_str) = TO_DAYS(NOW()); 耗时 0.15 sec 2，方式二，用date_format 函数 .
trunc效率比较
菜鸟的博客
08-10 560
trunc效率比较 select count(*) from balance_acct_item_payed where billing_cycle_id=202005 and trunc(created_date) = trunc(sysdate-70); select count(*) from balance_acct_item_payed where billing_cycle_id=202005 and to_char(sysdate-70,'yyyymmdd')=to_char(creat..
trunc() instr() substr()
好红的博客
12-02 1986
在这里整理一些oracle的函数，碰到的时候会总结进来，希望有点用
将精确到时分秒的日期截取到年月日
chenchenMonkey
01-02 1771
1. import time, datetime SXRQArray = time.strptime(str(self.a[z]['SXRQ']), "%Y-%m-%d %H:%M:%S") sxrqTime = time.strftime("%Y-%m-%d", SXRQArray) 2. import time, datetime self.sql3[0]['YJQYSJ'].str...
SQL Server中如何在date类型中提取年、月、日数据
qq_42027706的博客
07-29 1万+
SQL Server中如何在date类型中提取年、月、日数据
js Date 获取 年 月 日 具体的值
热门推荐
轻风细雨_林木木
04-05 3万+
var myDate = new Date(); myDate.getYear(); //获取当前年份(2位) myDate.getFullYear(); //获取完整的年份(4位,1970) myDate.getMonth(); //获取当前月份(0-11,0代表1月) myDate.getDate(); //获取当前日(1-31) myDate.getDay(); //获取当前星
Oracle date数据类型的字段截取年月日
mclt2017的博客
12-04 1万+
应用场景 ： 据库中存储的是 年月日时分秒 ， 但实际业务中可能只需要显示年月日 select to_char(字段名,'yyyy-mm-dd') from table；
java时间精确到天
你究竟是想一辈子卖糖水，还是希望获得改变世界的机会？
01-09 4426
public class DateTest { public static void main(String[] args) { System.out.println(new java.sql.Date(new java.util.Date().getTime()).toString());//输出 ：2014-01-09 } }
java中时间的截取 Date Calendar
u010325193的博客
01-23 3705
  推迟一周代码： Calendar curr = Calendar.getInstance(); curr.set(Calendar.DAY_OF_MONTH,curr.get(Calendar.DAY_OF_MONTH)+7); Date date=curr.getTime(); 推迟一月代码： Calendar curr = Calendar.getInstance(); cu...
ASP常用函数详解：从Array到DateAdd
以上仅是ASP中部分常用的函数，还有许多其他函数，如DateDiff()用于计算两个日期之间的差值，Left()和Right()用于截取字符串，Trim()用于去除字符串两端的空格等。了解并熟练运用这些函数能够极大地提高ASP开发的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值