很少受关注的SQL中一些函数调用秘密

jack198409

研究,研究

jolly10

进来学习！

anglei

看的不是很明白，晚上回去再看

jiqing1004

先留个名  明天上班了慢慢看

dingjun123

的确不容易，终于进来踩一下了！

newkid

呵呵，内部装修结束了？支持一下楼主的钻研精神。

关于#7, 可以用这个技巧：

SQL> exec pkg.reset_n;

PL/SQL procedure successfully completed.

SQL> select  * from (select pkg.inc_n n from t WHERE ROWNUM>0) where n = 2;

         N
----------
         2

照理说 NO_MERGE HINT 也可以，但我没试出来，不知道是什么道理。

参数不变时返回值也不变的函数叫 DETERMINISTIC 函数，在声明时可以加这个关键字，ORACLE会CACHE返回值。

另一个让ORACLE CACHE　住函数的方法是用SCALAR SUBQUERY:

SELECT ID, (SELECT DBMS_RANDOM.VALUE FROM DUAL) FROM T;

你会发现随机函数只发生了一次。

如果CBO认为每次子查询不一样，它还是会执行多次：

SELECT ID, (SELECT DBMS_RANDOM.VALUE FROM DUAL WHERE ID>0) FROM T;

函数调用次数是很难预测的，影响因素很多。到11G，函数的CACHE策略又发生了变化，不过我没有环境没法测。

等会有空我也贴几个有趣的例子上来。

jvkojvko

mark

newkid

原帖由 nyfor 于 2008-9-8 16:23 发表
以上出现在 order by, group by 表达式中的函数调用与出现在 where 中的还有一个不同就是后者的函数调用表达式不论是否与表数据有关, 均对每一行数据都调用一次(注意这是筛选之后的每一行)

在WHERE中的函数调用是会受索引影响的。
我先稍微改造一下你的PACKAGE,　增加一个inc2_n:

create or replace package pkg
is
  function get_n return number;
  function inc_n return number;
  procedure reset_n;

  function inc2_n(p_n in number) return number;

end;
/

create or replace package body pkg
is
  g_n number;
  function get_n return number
  is
  begin
    return g_n;
  end;
  
  function inc_n return number
  is
  begin
    g_n := g_n + 1;
    return g_n;
  end;
  
  procedure reset_n
  is
  begin
    g_n := 0;
  end;

  function inc2_n(p_n in number) return number
  is
  begin
    g_n := g_n + 1;
    return p_n;
  end;

begin
  g_n := 0;
end;
/


开始试验：
exec pkg.reset_n;

select * from t where id=pkg.inc2_n(2);

select pkg.get_n from dual;

     GET_N
----------
         3          ----- 全表扫描，执行了三次
         

--- 增加一个索引再做同样试验
create index t_x on t(id);

exec pkg.reset_n;

select * from t where id=pkg.inc2_n(2);

select pkg.get_n from dual;

     GET_N
----------
         2       ----- 走了索引，执行了两次，不知道为什么不是一次

newkid

用大一点的数据试验：

DELETE t;
INSERT INTO t (SELECT MOD(LEVEL,10) FROM DUAL CONNECT BY LEVEL<=1000);

SELECT COUNT(DISTINCT id) FROM T;

COUNT(DISTINCTID)
-----------------
              10


exec pkg.reset_n;

select pkg.inc2_n(id) from t;

select pkg.get_n from dual;

    GET_N
---------
     1000           ---- 每行调一次


改用SCALAR SUBQUERY:

exec pkg.reset_n;

select (SELECT pkg.inc2_n(id) FROM DUAL) from t;

select pkg.get_n from dual;

     GET_N
----------
        10        -------- 每个值调一次

改用重复率低一点的数据:
DELETE t;
INSERT INTO t (SELECT MOD(LEVEL,500) FROM DUAL CONNECT BY LEVEL<=1000);

SELECT COUNT(DISTINCT id) FROM T;

COUNT(DISTINCTID)
-----------------
             500


exec pkg.reset_n;

select pkg.inc2_n(id) from t;

select pkg.get_n from dual;

    GET_N
---------
     1000         ------ 还是1000次


改用SCALAR SUBQUERY:

exec pkg.reset_n;

select (SELECT pkg.inc2_n(id) FROM DUAL) from t;

select pkg.get_n from dual;

     GET_N
----------
       608         --- 不是500次了

nyfor

newkid 的研究更深入呀, 佩服, 给朵