一个Oracle写法的性能问题，查询中使用function性能大大降低！

Question

SELECT   order_code, sys_config_pkg.codeid2code (27, dosage_unit_id) drug_formFROM hospital_order ho
WHERE dc = 'N'                                  
ORDER BY ho.order_code;
SELECT  order_code,(SELECT code_name
FROM sys_code
WHERE type_id = 28 AND code_id = drug_form_id) drug_form
FROM hospital_order hoWHERE dc = 'N'
ORDER BY ho.order_code;
第一种写法，是考虑程序的结构性， 把从ID抓取Value写在了funciton里面， 当这样却大大影响了性能，需要耗时七八秒
第二种写法，把function里面的内容直接写出来，只要0.02秒！

Answer 1

这就得看函数sys_config_pkg.codeid2code ()里是读取了哪些内容？可能是里面读取某个表的资料，但表的资料太多了就慢

Answer 2

要看你函数怎么写的，
上次我有个函数，专门查询IP地址的
函数是这样的，调用一个函数把IP转换成数字，
原来写法在函数里，用一条SQL查询出来的，IP转换成数字直接在这条SQL里执行结果查询一天的数据要27分钟，后来我改了下，改成两条语句，先把IP转成数字，然后赋给变量，然后再查询
现在查询一天的大概6秒

Answer 3

函数如下，就是从一个系统参数表里面根据ID或者对应的Name, 数据量也不大
FUNCTION codeid2code (p_code_type_id IN INTEGER, p_code_id IN INTEGER)
RETURN VARCHAR2
    IS
        code   VARCHAR2 (150);
    BEGIN
        SELECT code_name
          INTO code
          FROM sys_code
         WHERE type_id = p_code_type_id AND code_id = p_code_id;        RETURN code;
    END codeid2code;

Answer 4

一般来说，使用函数的话oracle很难优化，使用查询的话oracle能够比较好的优化

Answer 5

使用函数的话，oracle得不停的的扫描sys_code表，而使用查询的话一遍就可以了，就sys_code的扫描次数而言估计是n:1的关系，n是结果集的条数

Answer 6

你比较一下，就会发现SQL用函数的话，递归调用(recursive calls)的次数明显增加。原因是自定义函数属于pl/sql，与SQL语句的执行属于不同的引擎。在SQL中如果调用自定义函数，那么将会在两个引擎间频繁切换，从而导致执行时间增长。
解决的办法也有，但是也是有条件的。
1.如果11g以下版本，并且表sys_code的数据是固定的，那么给函数加deterministic属性。
2.如果是11g或更高版本，那么给函数加RESULT_CACHE RELIES_ON(sys_code)属性

Answer 7

SELECT  order_code, 
(SELECT code_name 
FROM sys_code 
WHERE type_id = 28 AND code_id = drug_form_id) drug_form 
FROM hospital_order ho 
WHERE dc = 'N' 
ORDER BY ho.order_code; 
=>
select ho.order_code,sc.code_name drug_form 
FROM hospital_order ho,sys_code sc 
WHERE dc = 'N' and ho.drug_form_id=sc.code_id and sc.type_id = 28 
ORDER BY ho.order_code; LZ的语句一定要用function或子查询?就算两表不是1对1关系...用join也能解决了...