dbms=O10 oracle 10,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的方式就不会出现这样的问题

原因应如帖中nyfor回复的那样

“我觉得应该是 Oracle 优化器的BUG，对SQL的执行计划做了在某种极端条件下不等价的变换.

”