
Archives of the TeradataForumMessage Posted: Mon, 08 Jul 2002 @ 18:50:15 GMT
Doug has raised some interesting points here. First, if you would like an overview of how "random number" algorithms work, along with plenty of warnings about the possible pitfalls, I recommend Chapter 3 of Donald Knuth's Art of Computer Programming, Vol. 2 (3rd edition, AddisonWesley, 1998). There's a lot of higher math that I don't understand, but the main concepts are clear enough.
Yes, a new seed is used each time, but you have no control over it. The algorithm (lrand48, I believe) is described in the SQL Reference, Vol. 5 (V2R4.1, B0351101061A, June 2001), p. 743. Now, is the sequence "independent" of the prior sequences? Strictly speaking, no: the algorithm is deterministic; it's just designed to produce results that look random. Generally speaking, though, each sequence should look different enough from previous ones that a casual observer would never notice the pattern. Since you are trying to produce unique values, I guess you are no longer a casual observer!
In the words of the Wizard, you are a victim of disorganized thinking! If you pick two separate random sequences, uh, at random, they may very well overlap. For instance, let's say you generate two randomly selected groups of 10 integers from 0 to 99. There is a chance that they will contain one or more identical integers. (I'm not savvy enough to compute what that chance is, but it's greater than 0.) The same applies when you generate two groups of 100K random numbers from a universe of 2**31, although obviously the chances are smaller. And, of course, the algorithm isn't really random, so perhaps the chances are greater than they would be if the numbers were truly random. Demoralizing, isn't it? I believe the only way to accomplish what you wish is to generate a single set of random numbers and then somehow insert them 100,000 at a time into your final table. Hope this helps, wgr
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 27 Dec 2016  