使用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條甚至是多條記錄。