RE: Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <ian.cary@xxxxxxxxxxxxxx>, <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- Date: Wed, 28 Apr 2010 12:18:45 -0400
I used to use the dbms_random functions quite often with all its
idiosyncrasies. In a similar case to your I created a variable to store the
value and then used the variable in the query.
Declare
X number;
....
Begin
X:=trunc(dbms_random.value(1,500));
select col1
into ...
from sys.test1
where val1 = x;
end;
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ian Cary
Sent: Wednesday, April 28, 2010 11:22 AM
To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
Cc: jo.holvoet@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
oracle-l-bounce@xxxxxxxxxxxxx
Subject: RE: Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)
Hi Chris,
I think
select name,
executions
from v$db_object_cache
where name like 'DBMS_RANDOM%'
and type = 'PACKAGE BODY'
may give the verification you want (although the executions seem to be one
out).
You couild also compare your query with;
select * from sys.test1
where val1 = (select trunc(dbms_random.value(1,500)) from dual)
which only executes dbms_random.value once
Cheers,
Ian
|---------+--------------------------------->
| | ChrisDavid.Taylor@ingr|
| | ambarge.com |
| | Sent by: |
| | oracle-l-bounce@freeli|
| | sts.org |
| | |
| | |
| | 28/04/2010 15:55 |
| | Please respond to |
| | ChrisDavid.Taylor |
| | |
|---------+--------------------------------->
>---------------------------------------------------------------------------
-----------------------------------------------------------------------|
|
|
| To: jo.holvoet@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx,
oracle-l-bounce@xxxxxxxxxxxxx
|
| cc:
|
| Subject: RE: Here's a wierd one for you (SQL with
DBMS_RANDOM.VALUE)
|
>---------------------------------------------------------------------------
-----------------------------------------------------------------------|
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 test13 /----------
--------------------------------------------------abcde
SQL> /
no rows selected (?!)
SQL> /----------
--------------------------------------------------abcdefghijklmnopqrstuv
SQL> /
no rows selected
SQL> / (?!)----------
--------------------------------------------------abcdefghijklmnopqrs
SQL>
Thoughts?
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 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
For the latest data on the economy and society consult National Statistics
at http://www.ons.gov.uk
****************************************************************************
*****
Please Note: Incoming and outgoing email messages are routinely monitored
for compliance with our policy on the use of electronic communications
****************************************************************************
*****
Legal Disclaimer : Any views expressed by the sender of this message are
not necessarily those of the Office for National Statistics
****************************************************************************
*****
The original of this email was scanned for viruses by the Government Secure
Intranet virus scanning service supplied by Cable&Wireless Worldwide in
partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On
leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
