OracleBlog

Let me get your advice on this one.

Here's the situation, you need an evenly distributed sequence of random integers from 1 to 20. You decided to use the Oracle random number package 'dbmsrand'.

Incidentally, I decided to do it this way after searching Ask Tom.

http://asktom.oracle.com/~tkyte/Misc/Random.html
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:831827028200

There is a link to his web site to the right. Bookmark it.

While you're at it, bookmark Dan Morgan's page (link on the right).

http://www.psoug.org/reference/dbms_random.html

Ok, back to the story.

After you read the instructions on using it, you probably write something like this in your PL/SQL stored procedure:


AS
random_value number(2,0);
BEGIN
random_value := dbms_random.value(0,20);

Now you notice whoops, you're getting some 0s. You don't want 0. Plus you're not getting very many 20s. So you do this instead

random_value := dbms_random.value(1,21);

Much better! But whoops - you're getting the occasional 21! So you scratch your brain and do this:

random_value := dbms_random.value(1,21);
random_value := MOD(random_value, 20) + 1;

That should pour the 21s into the 1 pile, which you noticed is very, very slightly lower than the others.

Now you think you're getting the values you want. So here are my questions:

1. Will this truly generate an even sample of numbers from 1 to 20? Will there be the right number of 1s and 20s?
2. Is there a better way?
3. Say you're generating a very large sequence, thinking about performance, do you see any problems with this approach I should consider?

Here is the complete solution:

--------------------
@utlraw
@prvtrawb.plb
@dbmsoctk
@prvtoctk.plb
@dbmsrand

create table value_holder as (f_value NUMBER(2,0));

create or replace procedure ValuePlacer (number_to_generate IN number)
AS
random_value number(2,0);
BEGIN
for x in 1..number_to_generate
LOOP
random_value := dbms_random.value(1,21);
random_value := MOD(random_value, 20) + 1;
insert into value_holder values (random_value);
END LOOP;
END;

exec ValuePlacer(1000);

select f_value, count (f_value) from value_holder group by f_value;
-----------------

Thanks!

// posted by Robert Vollman @ Tuesday, May 24, 2005