Oracle中select xxx from dual优化

0    72    1

👉 本文共约870个字,系统预计阅读时间或需4分钟。

先看一个系统AWR的top CPU SQL:

img

其中排在第二位的SQL是这样的:

SELECT TO_CHAR(:B1 / (60 60 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

SQL虽然简单,但是执行次数却高达6.2亿次/天,消耗了大量的CPU资源。

经检查,这段SQL来自一个将number类型的时间字段转换成日期字符串的function。

这里面就存在2个问题:

1、使用number类型保存日期,使用起来非常不方便(使用varchar2类型保存日期也一样),建议使用date或timestamp保存日期类型。

2、使用sqlplus时,如果要计算一个值,我们会使用select xxx from dual;但是,在function、procedure、package、trigger中,这些纯计算的内容,就不再需要做select from dual的操作了,上面函数就可以将select xxx into ret_str from dual; return ret_str; 简写成:return xxx ;

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

原function代码示例如下:

CREATE OR REPLACE function number2date1(in_num number ) return varchar2

IS

ret_str varchar2(30);

BEGIN

SELECT TO_CHAR(in_num / (60 60 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')

into ret_str FROM DUAL;

RETURN ret_str;

END number2date1;

/

优化修改后的function代码如下;

CREATE OR REPLACE function number2date2(in_num number ) return varchar2

is

BEGIN

return TO_CHAR(in_num / (60 60 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') ;

END number2date2;

/

原function多次执行会消耗大量的recursive calls,经过上面的改写后,消耗的资源基本上可以忽略不计了。

简单的改写,节省了大量CPU资源,这就是SQL优化的力量。

参考

参考:老虎刘谈SQL优化

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

19 − 8 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部