Efficient Function Calls From SQL-在SQL语句中更高效的使用函数（转载）
网址：http://www.oracle-base.com/articles/misc/efficient-function-calls-from-sql.php
Efficient Function Calls From SQL
对于在SQL中更加高效的使用PL/SQL函数，本文提供了以下几种解决办法：
1、标量子查询
2、使用DETERMINISTC(确定性的；命运注定论的)关键字
3、使用session缓存（在不同的session中也可以共用），该方法只在11g之后的版本可以使用
4、使用PL/SQL集合进行手动的控制缓存
5、使用上下文进行手动的控制缓存
6、注意在WHERE语句后面使用的函数
有时，我们需要在一个查询的selelct的列表中中使用函数，这可能是内置的函数，或者我们自定义的函数，默认情况下，对于查询返回的每一行，我们都将进行函数的调用，如果函数是deterministic（确定的），对于相同的输入参数，他都会返回确定的结果，和总的记录数相比，如果唯一性的参数使用的很低（在总的记录中，唯一性的值比例很高，相同的值很少），那么这可能效率有点低。
下面的例子建立了一个有十条记录的测试表，大部分包含相同的值，下面建立的函数中调用了DBMS_LOCK.SLEEP,代表了内部处理的工作量，函数的每一次调用都将花费一秒钟，该函数将应用在随后查询测试表的SQL语句中。
CREATE TABLE func_test ( id NUMBER ); INSERT INTO func_test SELECT 1 FROM dual CONNECT BY level <= 9; INSERT INTO func_test VALUES (2); COMMIT; CREATE OR REPLACE FUNCTION slow_function (p_in IN NUMBER) RETURN NUMBER AS BEGIN DBMS_LOCK.sleep(1); RETURN p_in; END; /
调用函数进行测试：
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
2
10 rows selected
Executed in 10.031 seconds
SQL>
花费的时间清晰的表明了函数被调用了十次，每一行调用了一次，即使对于输入的参数是相同的，但是它还是一行调用了一次。
你第一件应该做的事情是：尝试这去删除一些不必要的函数调用，这听起来很明显，对于一些懒惰的和缺乏SQL开发经验的开发者来说，可能在可以使用SQL代替的地方，他却使用了PL/SQL，SQL包含一些特性，例如分析函数，可以做一些不可思议的事情，而不需要使用PL/SQL。如果上述情况不可能的话，下面的几种方法能够减少Oracle在调用函数的过程中的负载。
记住：对于任何在缓存方法起到显著效果的先决条件是在结果集中重复记录很多，并且对于独立的数据它们是稳定的-即值
不会发生变化，如果函数依赖的表或者视图存在不稳定的数据，她们缓存中的数据很容易过期，而变得不准确。
标量子查询缓存
将函数调用写入标量子查询，允许Oracle使用标量子查询的缓存去优化最终需要调用函数的次数。
SQL> SET TIMING ON
SQL> SELECT (SELECT slow_function(id) FROM dual)
2 FROM func_test;
(SELECTSLOW_FUNCTION(ID)FROMDU
------------------------------
1
1
1
1
1
1
1
1
1
2
10 rows selected
Executed in 2.078 seconds
花费的时间表明Oracle对子查询的结果进行了缓存，在必要的地方对缓存进行了重用。
DETERMINISTC关键字
DETERMINSTIC关键字允许你指明一个函数是确定性的，在8i后该就可以使用了，但是直到10g的R2版之后，才对在SQL中使用的函数有一些作用。
在随后的例子中，函数被声明为deterministic可以提高性能。
CREATE OR REPLACE FUNCTION slow_function (p_in IN NUMBER) RETURN NUMBER DETERMINISTIC AS BEGIN DBMS_LOCK.sleep(1); RETURN p_in; END; /
结果：
SQL>
SQL> SET TIMING ON
SQL> SELECT slow_function(id)
2 FROM func_test;
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
2
10 rows selected
Executed in 2.031 seconds
使用11G提供的PL/SQL函数的缓存机制（对于不同的会话之间可以共用）
11G引进了如下的两种缓存机制：
下面我们使用第一种机制进行查询结果的缓存，允许我们移除对于相同输入参数的调用。
CREATE OR REPLACE FUNCTION slow_function (p_in IN NUMBER) RETURN NUMBER RESULT_CACHE AS BEGIN DBMS_LOCK.sleep(1); RETURN p_in; END; /
结果：
SET TIMING ON
SELECT slow_function(id)
FROM func_test;
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
2
10 rows selected.
Elapsed: 00:00:01.99
使用上述方式的缓存信息可以被其他的会话使用，它依赖的对象是自动被管理的：
关于数据一致性的问题，如下所述：如果我们修改了函数依赖的表的数据，我们仍然获取了最快的查询时间，那么表明
缓存中的数据仍然被使用，这就导致了一个潜在的数据不一致的问题，使用选项-RELIES_ON语句用来指明以来的表和视
图，所以，当缓存依赖的表或者视图发送了修改，缓存会失效。
参考网址：http://www.oracle-base.com/articles/11g/cross-session-plsql-function-result-cache-11gr1.php
使用PL/SQL的集合进行手动的管理缓存信息
CREATE OR REPLACE PACKAGE cached_lookup_api AS
FUNCTION get_cached_value(p_id IN NUMBER) RETURN NUMBER;
FUNCTION slow_function(p_in IN NUMBER) RETURN NUMBER;
END cached_lookup_api;
/
CREATE OR REPLACE PACKAGE BODY cached_lookup_api AS
TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
g_tab t_tab;
g_last_use DATE := SYSDATE;
g_max_cache_age NUMBER := 10 / (24 * 60); -- 10 minutes
-- -----------------------------------------------------------------
FUNCTION get_cached_value(p_id IN NUMBER) RETURN NUMBER AS
l_value NUMBER;
BEGIN
IF (SYSDATE - g_last_use) > g_max_cache_age THEN
-- Older than 10 minutes. Delete cache.
g_last_use := SYSDATE;
g_tab.delete;
END IF;
BEGIN
l_value := g_tab(p_id);
EXCEPTION
WHEN no_data_found THEN
-- Call function and cache data.
l_value := slow_function(p_id);
g_tab(p_id) := l_value;
END;
RETURN l_value;
END get_cached_value;
-- -----------------------------------------------------------------
-- -----------------------------------------------------------------
FUNCTION slow_function(p_in IN NUMBER) RETURN NUMBER AS
BEGIN
dbms_lock.sleep(1);
RETURN p_in;
END;
-- -----------------------------------------------------------------
END cached_lookup_api;
/
测试:
SQL> SET TIMING ON
SQL> SELECT cached_lookup_api.get_cached_value(id)
2 FROM func_test;
CACHED_LOOKUP_API.GET_CACHED_V
------------------------------
1
1
1
1
1
1
1
1
1
2
10 rows selected
Executed in 2.047 seconds
对于上述的处理方式，有以下几点说明：
1、在缓存中没有依赖性的管理，只是删除了超过十分钟的缓存数据，你可以提高间隔尺寸，但是它可能需要额外的工作。
2、如果会话是连接池的一部分，对于多次调用，可能会发生信息的溢出你可以使用 package reset.来解决这个问题。
3、这个方法没有自动管理缓存大小的机制。
4、缓存信息在多个会话中不可以进行共享。
手动的使用上下文控制缓存
在where语句中使用函数
在where子句中使用将函数应用到某列上，可能导致性能降低，因为它阻止了Oracle的优化器在该列上面使用所有，假定
该语句不可以删除该函数的调用，那么其中的一个选择是使用函数索引：function based index.
也可以考虑使用11G引入的虚拟列-virtual columns
