RE: Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "'Holvoet, Jo'" <jo.holvoet@xxxxxxxxxxxxx>, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>, "'oracle-l-bounce@xxxxxxxxxxxxx'" <oracle-l-bounce@xxxxxxxxxxxxx>
- Date: Wed, 28 Apr 2010 09:55:56 -0500
If so, the function dbms_random.value would be executed 500 times, right? (500
rows in the table) and only return the rows where it "hits"?
Anyway to verify this?
I ran a sql trace on the select that returned 2 rows here's what the trace has:
I looked for other instances of "random" in the tkprof output but didn't see
any.
select * from test1
where val1 = trunc(dbms_random.value(1,500))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 56 (TAYLORCD)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL TEST1 (cr=8 pr=0 pw=0 time=2393 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: CHOOSE
2 TABLE ACCESS (FULL) OF 'TEST1' (TABLE)
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and
may also be privileged. If you are not the named recipient, please notify the
sender immediately and delete the contents of this message without disclosing
the contents to anyone, using them for any purpose, or storing or copying the
information on any medium.
-----Original Message-----
From: Holvoet, Jo [mailto:jo.holvoet@xxxxxxxxxxxxx]
Sent: Wednesday, April 28, 2010 9:47 AM
To: Taylor, Chris David; oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Subject: RE: Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)
Doesn't it evaluate dbms_random.value for every row ?
mvg / regards,
Jo Holvoet
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Taylor, Chris David
Sent: woensdag 28 april 2010 16:40
To: 'oracle-l@xxxxxxxxxxxxx'; 'oracle-l-bounce@xxxxxxxxxxxxx'
Subject: Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)
(First one didn't seem to go through to the list - resending as plain txt)
DB Version:
Server 10.2.0.4 CPU24 Windows 64Bit
Client:
10.2.0.4 CPU24 Windows 32bit
I haven't researched this yet, but I'm curious why this might be happening.
Here's the test setup:
create table test1
(val1 number,
val2 varchar2(50)
)
/
Table Created
declare
v1 number := 1;
v_text varchar2(26);
begin
for i in 1..500
loop
select substr('abcdefghijklmnopqrstuvwxyz',1,dbms_random.value(1,26))
into v_text
from dual;
insert into test1
(val1, val2)
values
(v1, v_text);
v1 := v1+1;
end loop;
end;
/
500 Rows Inserted
Commit
/
Here is the interesting part (to me) - Sometimes it returns 1 row, sometimes it
returns more than 1 row, and sometimes it returns no rows.
To my mind, "trunc(dbms_random.value(1,500)" should return a random value
between 1 & 500 every time and just one value. Is this weird, or is it just
me???
SQL> select * from test1
2 where val1 = trunc(dbms_random.value(1,500))
3 /
VAL1 VAL2
---------- --------------------------------------------------
307 abcde
SQL> /
no rows selected (?!)
SQL> /
VAL1 VAL2
---------- --------------------------------------------------
2 abcdefghijklmnopqrstuv
SQL> /
no rows selected
SQL> / (?!)
VAL1 VAL2
---------- --------------------------------------------------
87 abcdefghijklmnopqrs
259 abcde
SQL>
Thoughts?
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxx
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and
may also be privileged. If you are not the named recipient, please notify the
sender immediately and delete the contents of this message without disclosing
the contents to anyone, using them for any purpose, or storing or copying the
information on any medium.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
