使用DBMS_RANDOM过程引发的问题

这个问题其实是昨天问题所引发的。

动态返回结果的视图：http://yangtingkun.itpub.net/post/468/490262

 

 

在上面的文章结尾提到了，利用变量构造动态视图，可以建立很多有意思的对象。这里就引出了一个有意思的问题，不过这个问题和动态视图无关，而和最后一个使用DBMS_RANDOM包的例子有关。

SQL> create table t_id (id number);

Table created.

SQL> insert into t_id 
  2  select rownum
  3  from dba_objects
  4  where rownum <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

最开始的时候，V_RANDOM视图的定义和上一篇文章中的并不一致。当时的视图是这样建立的：

SQL> create or replace view v_random
  2  as select *
  3  from t_id
  4  where id = round(dbms_random.value(1, 10000));

View created.

建立这个视图后，发现这个视图的访问结果很奇怪：

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

        ID
----------
      4325

SQL> select * from v_random;

        ID
----------
      4012

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

        ID
----------
      1192

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

        ID
----------
      5860

SQL> select * from v_random;

        ID
----------
      5922
      8077

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

        ID
----------
      3368
      3449

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

        ID
----------
      5683
      1551
      4929

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

no rows selected

SQL> select * from v_random;

        ID
----------
       762

不光返回ID的值是随机的，连返回结果的记录数也是不确定的。有时返回1条、有时返回2条甚至3条，还有时根本不返回任何记录。

开始以为DBMS_RANDOM包的用法有误，简单分析后发现问题和DBMS_RANDOM包有些关系，但是并非是用法错误。

原因在于DBMS_RANDOM.VALUE函数的调用并非是发生在语句执行之前，而是发生在语句运行之中。这样说有点抽象，下面具体的描述一下。

以前写过一篇文章，介绍Oracle是如何处理常量的：http://yangtingkun.itpub.net/post/468/20038

在文章中提到，对于常量而言，Oracle会在SQL语句执行之前将结果计算出来，而对于列的运算而言，需要对表中每条记录都进行运算。

如果Oracle这里采用常量的计算方法，就会先执行DBMS_RANDOM.VALUE函数，从而得到一个固定的结果，那么最终必然返回1条记录。实际上也就是上一篇文章中展示的例子。

而现在的情况是，Oracle没有提前计算DBMS_RANDOM.VALUE的值，而是针对T_ID表扫描的每条记录都去调用函数DBMS_RANDOM.VALUE并和ID的值进行比较。显然二者相等的概率极小，只有万分之一的可能性。但是由于T_ID包含1W条记录，因此这个概率极小的相等比较了1W次，导致最终查询结果很可能返回1条甚至是多条记录。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-612645/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-612645/