### Tuesday, May 24, 2005

## Random Numbers

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!

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!

Comments:

<< Home

I'm not sure if I could follow your example, but it seems to me that what

you originally where after was this:

SQL> select rownum from all_objects where rownum < 21 order by dbms_random.random;

ROWNUM

----------

19

10

11

15

9

1

14

16

12

7

2

8

4

6

17

13

5

3

20

18

HTH

Holger

you originally where after was this:

SQL> select rownum from all_objects where rownum < 21 order by dbms_random.random;

ROWNUM

----------

19

10

11

15

9

1

14

16

12

7

2

8

4

6

17

13

5

3

20

18

HTH

Holger

Holger's solution is perfect for creating a random sequence. If your aim was to populate a table with randomly (and uniformly!) generated integers from 1 to 20:

SQL> select ceil(dbms_random.value(0,20)) rand_int from all_objects where rownum < 10000;

Yes, in theory this could produce a zero value if the randomly generated real number was exactly zero. However given that Oracle's precision is 38 digits the chance of that happening is pretty slim to say the least.

HTH,

Phil

Post a Comment
SQL> select ceil(dbms_random.value(0,20)) rand_int from all_objects where rownum < 10000;

Yes, in theory this could produce a zero value if the randomly generated real number was exactly zero. However given that Oracle's precision is 38 digits the chance of that happening is pretty slim to say the least.

HTH,

Phil

<< Home