dbms_random在Oracle 10.2.0.4下的bug

Test Case：
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
CREATE TABLE TestCode
(
ID VARCHAR2(5),
VAL NUMBER(1)
);

select * from (
select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
from dual connect by rownum<=100
)
where val>0;

insert into testcode select * from (
select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
from dual connect by rownum<=100
)
where val>0;

select * from testcode;
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

 

執行一下，偶這裡的情況是：
SQL> select * from (
2 select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
3 from dual connect by rownum<=100
4 )
5 where val>0;

ID VAL
---------- ----------
30 1
40 1
54 1
75 1
91 1

SQL> /

ID VAL
---------- ----------
1 1
3 1
5 1
13 1
22 1
24 1
29 1
37 1
38 1
54 1
60 1

ID VAL
---------- ----------
62 1
93 1
96 1

已選擇14行。


插入到表中
SQL> insert into testcode select * from (
2 select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
3 from dual connect by rownum<=100
4 )
5 where val>0;

已建立6行。


查詢一下看看
SQL> select * from testcode;

ID VAL
----- ----------
18 0
21 0
22 0
33 0
60 0
94 0

已選擇6行。

VAL值有問題，插入到表中的資料，VAL值應該都是1才對

似乎問題和呼叫dbms_random有關
請看下面的測試例子

SQL> select * from (select rownum+100 id, decode(sign(12-rownum),-1,1,0) val fro
m dual connect by rownum<20) where val>0;

ID VAL
---------- ----------
113 1
114 1
115 1
116 1
117 1
118 1
119 1

已選擇7行。

SQL> insert into testcode select * from (select rownum+100 id, decode(sign(12-r
ownum),-1,1,0) val from dual connect by rownum<20) where val>0;

已建立7行。

SQL> select * from testcode;

ID VAL
----- ----------
18 0
21 0
22 0
33 0
60 0
94 0
113 1
114 1
115 1
116 1
117 1

ID VAL
----- ----------
118 1
119 1

已選擇13行。

但使用CTAS的方式就不會出現這樣的問題

論壇帖子：http://www.itpub.net/viewthread.php?tid=1059418

原因應如帖中nyfor回覆的那樣
“我覺得應該是 Oracle 最佳化器的BUG，對SQL的執行計劃做了在某種極端條件下不等價的變換.
”