Home » SQL & PL/SQL » SQL & PL/SQL » Random number generator
icon5.gif  Random number generator [message #167528] Thu, 13 April 2006 10:02 Go to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

Hi,

I've been asked to provide 8,000 "truly random" numbers for a marketing campaign (they will be used by customers to enter a restricted prize draw).

My question is, can I just create a sequence of distinct random numbers using the dbms_random.random.value function, or would that not be sufficiently "random"?

Fred
Re: Random number generator [message #167530 is a reply to message #167528] Thu, 13 April 2006 10:12 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You'd need to get them to define exactly what they mean by truly random. Check out google for PRNG and look at some of the concepts.

Jim
Re: Random number generator [message #167532 is a reply to message #167528] Thu, 13 April 2006 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I've been asked to provide 8,000 "truly random" numbers for a marketing campaign
It does not matter at all whether the assigned numbers are "random" (or just sequential).
What does matter is how the "winning" number is selected. It should be "random", but within the assigned range.
Re: Random number generator [message #167533 is a reply to message #167532] Thu, 13 April 2006 10:28 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
select owner, rownum from all_objects
where rownum<100
order by dbms_random.value(1,100);
Play with the above code.
Re: Random number generator [message #167578 is a reply to message #167533] Fri, 14 April 2006 02:09 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Or this (sorry Jim, but in my experience this is faster):
SELECT lv
FROM   ( SELECT LEVEL lv
         FROM   dual
         CONNECT BY LEVEL < 101
       )
ORDER BY dbms_random.value(1,100)
/


MHE
Re: Random number generator [message #167834 is a reply to message #167578] Mon, 17 April 2006 07:05 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
@Maarten, cheers for that, (filed away for future use Smile )
Jim

[Updated on: Mon, 17 April 2006 07:06]

Report message to a moderator

Re: Random number generator [message #167971 is a reply to message #167834] Tue, 18 April 2006 03:29 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Ah well, it's just another row generator. I've added an article on this particular subject in the OraFAQ wiki. Nothing spectacular and nothing new but it might be interesting. Wink

MHE
Re: Random number generator [message #168047 is a reply to message #167528] Tue, 18 April 2006 09:39 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I might be being a little dense, but why in the order by is it dbms_random.value(1,100) instead of just dbms_random.value?
Re: Random number generator [message #168058 is a reply to message #168047] Tue, 18 April 2006 10:02 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
smartin wrote on Tue, 18 April 2006 16:39

I might be being a little dense, but why in the order by is it dbms_random.value(1,100) instead of just dbms_random.value?

http://www.orafaq.com/forum/fa/450/0/ No reason I guess. Would it affect performance?

MHE
Re: Random number generator [message #168059 is a reply to message #167528] Tue, 18 April 2006 10:06 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Performance wise, I don't know...

Mainly I was just making sure there wasn't something I was missing.

I guess if it isn't needed then I'd leave it out, under the reasoning of "One less thing".

Also dual does seem to be quite fast, I believe it was 10gR1 that had a special optimization for it to not do any logical I/O at all, whereas there would be i/o for dictionary objects.
Re: Random number generator [message #168060 is a reply to message #168059] Tue, 18 April 2006 10:10 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Right, and good you added that info to the wiki article! Thumbs Up

MHE
Re: Random number generator [message #168304 is a reply to message #167528] Wed, 19 April 2006 12:52 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
If Fred needs "truly random" numbers, may be using of crypto random generators will be better than DBMS_RANDOM.

Using of DBMS_CRYPTO_TOOLKIT or DBMS_CRYPTO is recommended by documentation to get cryptographycally strong randoms.

declare 
 RN1 number;
 RN2 number;
 RB RAW(16); l number:=16;
begin
 RN1 := DBMS_RANDOM.VALUE;
 dbms_output.put_line('DBMS_RANDOM.VALUE               :'||RN1);

 dbms_crypto_toolkit.Initialize;
 dbms_crypto_toolkit.SEEDRANDOM(null,12345312);

 RN2 := DBMS_CRYPTO_TOOLKIT.RANDOMNUMBER(null);
 dbms_output.put_line('DBMS_CRYPTO_TOOLKIT.RANDOMNUMBER:'||RN2);

 RB := DBMS_CRYPTO_TOOLKIT.RANDOMBYTES(null,l);
 dbms_output.put_line('DBMS_CRYPTO_TOOLKIT.RANDOMBYTES HEX:'||rawtohex(RB));
 RN1 := to_number( rawtohex(RB), rpad('x',l*2,'X'));
 dbms_output.put_line('DBMS_CRYPTO_TOOLKIT.RANDOMBYTES NUM:'||RN1);
end;
/

Re: Random number generator [message #168546 is a reply to message #168304] Thu, 20 April 2006 22:27 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:3873302221732130623::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:831827028200

Hope this helps...
Hobbes
Previous Topic: result in an order
Next Topic: dbms_job : Scheduling accuracy
Goto Forum:
  


Current Time: Tue Aug 26 22:39:55 CDT 2025