WEB开发网
开发学院数据库Oracle oracle自定义函数的执行时间 阅读

oracle自定义函数的执行时间

 2007-06-02 16:09:08 来源:WEB开发网   
核心提示:oracle中的pl/sql代码会涉及到两个执行引擎,一个专门处理标准的SQL语句,oracle自定义函数的执行时间,另外一个处理pl/sql的过程代码,一般在引擎切换上会带来cpu额外的开销,结果版本2比1快3倍,从中得出的结论,比如foreach循环语句和普通for循环的区别,就是foreach消除了引擎切换

oracle中的pl/sql代码会涉及到两个执行引擎,一个专门处理标准的SQL语句,另外一个处理pl/sql的过程代码,一般在引擎切换上会带来cpu额外的开销。比如foreach循环语句和普通for循环的区别,就是foreach消除了引擎切换,一直驻留在执行标准SQL的引擎中,从而缩短了执行时间。
现在有一个客户信息表tacustomer, 包含了birthday, certificatetype, certificateno等字段,现在想要获知客户的生日信息。由于客户注册时生日等字段是可选择填写项,故大多数为空,所以要从证件号码certificateno中提取,certificatetype为证件类型,除身份证之外还有军官证、士兵证、户口本等枚举值,输入的值也不可靠,本来'0'代表身份证,但由于录入错误,使得非'0'值可能也指身份证,而'0'也可能输入的不是身份证。结合这些考虑因素,写了一个函数,输入参数为上述三个字段,输出为生日的年份。写了两个版本,在笔者的windows2003数据库服务器上(Intel(R) Xeon(R) CPU 5140 @ 2.33GHZ,4cpu,4.00GB内存),执行语句如下(tacustomer行数为200百万左右): -- 测试简单字符串连接的执行时间
SELECT COUNT(t.certificatetype || t.certificateno || t.birthday)
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000

-- 测试自定义函数的执行时间
SELECT COUNT(f_extract_birthday(t.certificatetype, t.certificateno, t.birthday))
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000

执行时间如下(秒):
8.563
19.844(版本1)
57.953(版本2)

可以看出基本是3倍的关系(将上面的条件改为ROWNUM < 1000000,同样满足3倍的关系),版本1和版本2之间的区别是内部实现不同。第一代身份证15位必须全为0-9的数字,第二代18位的前17位全为数字,最后一个为数字或'X'。版本1和2对这个是否数字的校验采用了不同的方式。版本1逐个检查,版本2用cast(certno as numeric)并捕捉异常的方式,结果版本2比1快3倍。从中得出的结论,自定义函数一般性能比不上系统内建函数。

附两个版本的代码:

--版本1
create or replace FUNCTION f_extract_birthday(id in VARCHAR, birthday in VARCHAR)
RETURN VARCHAR
IS
i integer;
--id VARCHAR(18);
--birthday VARCHAR(8);

yyyy VARCHAR(4);
len SMALLINT;
trans BOOLEAN;
c SMALLINT;
ret VARCHAR(4);
val NUMERIC(18, 0);
BEGIN
i := 1;
--id := '430302810315405';
--id := '43030219810315405X';
--birthday := '19790315';
trans := FALSE;
len := length(id);
--val := CAST(id AS NUMERIC);
--dbms_output.put_line(val);

1 2 3  下一页

Tags:oracle 定义 函数

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接