代码中误用select xxx from dual案例一则
代码中误用select xxx from dual案例一则
先看一个系统AWR的top CPU SQL：
其中排在第二位的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 ;
原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优化的力量。
本文分享自 老虎刘谈oracle性能优化 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与?腾讯云自媒体同步曝光计划? ，欢迎热爱写作的你一起参与！