Re: Here's a wierd one for you (SQL with DBMS_RANDOM.VALUE)
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Wed, 28 Apr 2010 17:15:13 +0200
Chris,
I have the same kind of results as you on 11gR2/Linux.
I have experienced a lot of weirdness while working on the
generation of test data for one of my books; one weird result was that
if I was calling random functions multiple times in the select list,
there was a very neat decrease of randomness from left to right; I
haven't measured it precisely (I haven't volume 2 of Knuth, only 1 and 3
;-)) but it was obvious.
My conclusion was that, as far as random functions are concerned,
PL/SQL is better than SQL (you will not see me write something like that
very often). I have no idea how their random functions are implemented,
but I suspect some tricks involving buffering so as to avoid computing
things too often (for instance, when you compute normally distributed
random variables, you generally compute two values at once that are the
coordinates of a point in a disc, every second call computes nothing). I
must add that random functions are sinful functions par excellence - you
cannot have anything less deterministic. Any trick the optimizer tries
on then usually backfires.
You can avoid (I think) no data found by writing
select * from test1
cross join (select trunc(dbms_random.value(1,500)) n
from dual
where rownum > 0) x
where val1 = x.n
/
which will force the evaluation of the random value each time you
execute the query. But you'll still have multiple rows very often. From
my experiments (I have added the rownum to the select list), it's the
inner query that returns several rows with a rownum value of 1.
If you add
and rownum = 1
to the outer query, then everything looks normal - but of course, I
don't find this very satisfying, from an intellectual point of view.
File it under "Oracle dirty tricks".
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Taylor, Chris David wrote:
> (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:
