ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DATE类型截取到天的效率

DATE类型截取到天的效率

原创 Linux操作系统 作者:yangtingkun 时间:2012-01-09 23:57:48 0 删除 编辑

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_CHAR1/8的性能提升,对于执行计划完全相同的情况而言,这个比率已经很高了。

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

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

 

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

上一篇: ORA-600(16608)错误
下一篇: ORA-600(13013)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10389506