oracle自定义函数的执行时间[1]

文章作者 100test 发表时间 2007:06:08 11:29:48
来源 100Test.Com百考试题网

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).

if (len = 15) then

src="/oracle/js/wxgg_oracle.js">