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

junsansi

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

5.
SQL> exec pkg.reset_n;

PL/SQL procedure successfully completed
SQL> select pkg.inc_n from t where pkg.inc_n = 1;

     INC_N
----------
         2
         3
         4
SQL> select pkg.get_n from dual;

     GET_N
----------
         4

SQL>
SQL> exec pkg.reset_n;

PL/SQL procedure successfully completed
SQL> select pkg.inc_n from t where pkg.inc_n = 2;

     INC_N
----------
SQL> select pkg.get_n from dual;

     GET_N
----------
         1

   以上两条结果似乎不是我们所想象的.
SQL> exec pkg.reset_n;

PL/SQL procedure successfully completed
SQL> select  * from (select pkg.inc_n n from t ) where n = 1;

         N
----------
         2
         3
         4
SQL> select pkg.get_n from dual;

     GET_N
----------
         4
SQL> exec pkg.reset_n;

PL/SQL procedure successfully completed
SQL> select  * from (select pkg.inc_n n from t ) where n = 2;

         N
----------
SQL> select pkg.get_n from dual;

     GET_N
----------
         1

这里我们发现Oracle对于复杂的查询做优化后导致执行结果不是我们想要的.
对于前两个SQL, Oracle 的执行路径已经转换为后面两个了. 那么我们有什么办法来防止这种情况发生呢, 禁止外层where谓词push 到内部查询, 但是Oracle好像又没有这样的 hint 可用. 目前我是没有找到什么好办法来阻止这种错误结果的发生.
这个简单：select  * from (select pkg.inc_n n,rownum rn from t ) where n = 1;即可

nyfor

select  * from (select pkg.inc_n n,rownum rn from t ) where n = 1;
真是很奇怪啊, 加了个 Rownum 列它就正确了.

Oracle 好深奥哦...

个人觉得: 函数调用次数的不确定性, 以及某些SQL产生我们不期望的结果, 都归结于Oracle的优化器的优化原理.

nyfor

newkid 讲的 DETERMINISTIC 函数, 以及函数返回值的 cache 策略, 其实都不太重要,
因为 DETERMINISTIC 函数的调用次数不确定并不会影响SQL查询的结果.
而非 DETERMINISTIC 函数就麻烦了, 极有可能影响SQL查询的结果.

newkid

呵呵，我是在你启发下做了些试验，知其然不知其所以然。
不知你们看了我#18的贴没有？用ROWNUM的效果我也是知道的，这是为了防止PUSH谓词，但我不知道NO_MERGE HINT怎么不起作用呢？你们用NO_MERGE试过没有？
要说这个研究成果，我认为有以下几点：
1.不要依赖于函数调用次数。在函数里不要有修改上下文相关的操作。
2.第1点做到之后，我们就要尽量减少函数调用，提高效率。
2.a 在你理解了DETERMINISTIC 函数的前提下，尽量用DETERMINISTIC 函数。典型的应用例子：代码转名称，名称转代码。
2.b 在非DETERMINISTIC 函数时，也要尽量让ORACLE CACHE住你的函数结果。可用技巧就是SCALAR SUBQUERY.

nyfor 你认为1000次调用和10次调用没有区别吗？哪个效率高？尽管结果都是一样的。

请继续讨论哈，明天我再来。

dreamroot

虽然我是菜鸟，但感觉你的研究很无聊和没有必要性。一般很少有人会用dbms_random.value进行随机查询的。而且在dbms_random.value<dbms_random.value.这样的where条件，我想大概没有人会用到的，对于一个case来说，总有其明确的需求，则同样带来了明确的where条件。不会用dbms_random.value <dbms_random.value. 作为无聊的条件的. oracle的聚组函数其功用是固定的，唯一变化的只是随查询的数据的变化而变化，其值也只会返回唯一值.用户需求的也就是可以确定的期望值.在程序中如果多次调用，建议每次在调用之前给个初值，则无论调用多少次都不会带来错误的结果的.
个人浅见，仅供参考.

nyfor

原帖由 newkid 于 2008-9-9 10:17 发表
呵呵，我是在你启发下做了些试验，知其然不知其所以然。
不知你们看了我#18的贴没有？用ROWNUM的效果我也是知道的，这是为了防止PUSH谓词，但我不知道NO_MERGE HINT怎么不起作用呢？你们用NO_MERGE试过没有？
要说这个研究成果，我认为有以下几点：
1.不要依赖于函数调用次数。在函数里不要有修改上下文相关的操作。
2.第1点做到之后，我们就要尽量减少函数调用，提高效率。
2.a 在你理解了DETERMINISTIC 函数的前提下，尽量用DETERMINISTIC 函数。典型的应用例子：代码转名称，名称转代码。
2.b 在非DETERMINISTIC 函数时，也要尽量让ORACLE CACHE住你的函数结果。可用技巧就是SCALAR SUBQUERY.

nyfor 你认为1000次调用和10次调用没有区别吗？哪个效率高？尽管结果都是一样的。

请继续讨论哈，明天我再来。

我看了你的那个帖子的. 也看到了使用 rownum 后的效果, 只是后来三思再次提起, 我就回复了一下.
no_merge 之前我没试过, 刚试了, 真的不行.
1000次调用和10次调用当然有性能上的区别, 我说的不太重要的原因是说 不会影响到结果的正确性, 也就是说至少不会让我们的代码出现BUG.

junsansi

to:nf
不奇怪，rownum固化结果集，不好使才叫奇怪~~

to:newkid
no_merge不会有效，原因很简单，看看该语句的执行计划就明白了~

nyfor

DETERMINISTIC 函数特性在 Oracle 10G 才得以实现.
9I 中还无效, 虽然可以定义这样的函数.

nyfor

原帖由 junsansi 于 2008-9-9 10:43 发表
to:nf
不奇怪，rownum固化结果集，不好使才叫奇怪~~

嗯, 有道理.

kmpx

先收藏