Archives of the TeradataForum

Message Posted: Fri, 28 Jun 2002 @ 14:06:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Does anyone have any tips to force the random number function to generate more uniform distributions over multiple requests? The random number function provides a very uniform distribution in the context of a single request. That is to say that I can generate an 18 digit random number over 1 billion rows without hitting any duplicates if they are all done through one single SQL statement. I am currently using the following form to populate a DECIMAL(18,0) column:

(RANDOM(0,999999999)(format'999999999'))||(RANDOM(0,999999999)(format'999999999'))

Given an 18 digit integer I would expect that the chances of hitting duplicates within a population of 1 billion rows would be 1 out of 1 billion assuming a perfect uniform distribution.

The trouble arises when I attempt to split this process into multiple passes inserting a batch of rows (100K) at a time. On the second or subsequent pass of the same SQL I begin to see some number ranges that have a high rate of duplication in each pass. The documentation seems to infer that a different seed to the algorithm is used for each call which should produce another totally random number sequence independent of any prior sequences. However, this does not seem to be the case.

I understand that all random number generators cannot produce a totally uniform distribution so I am looking for ideas on how to workaround this issue so that I can generate a set of random numbers without duplicates with some level of confidence.

I would appreciate any comments.

Doug Drake

	<Prev	Next>		<<First	<Prev	Next>	Last>>