Re: Random Sample Using SQL?

Kendall Willets (TV DBA) wrote:
>
> Brian Ewins wrote:
> >
> > Kendall Willets (TV DBA) wrote:
> > How about using a random function of ROWNUM? That would provide you
> > > (snip)
> > >
> >
> > This won't work. Consider: until Random_fn(n)<=.10, ROWNUM will never
> > reach n+1. And unless Random_fn(1)<=.10 you generate *nothing* at all.
>
> Correct. Another person emailed me with this info and suggested a
> solution: use a view which includes rownum as a column (haven't tested
> this yet).
>
> (The problem is that rownum is only incremented when a row is returned -
> not each time one is tested as above)
>
> (time granularity is to the second). However, creating a numeric
> > hash from a string representation of a rowid, and the time, could
> > give you a pretty random number. I'll leave this as an excercise ;o)
> >
> I like the rowid suggestion. One related idea which might work with
> oracle and other db's supporting hashed tables:
>
> (snip)

There is a simple and elegant solution to this problem. I (along with many others) have a PL/SQL random number generator (reurning a number between 0 and 1 - 1.0E38). Create an overlaid definition of the function which is defined to take an argument of datatype rowid. This argument does not need to be used (or even referenced) in the overlaid definition, which merely returns the random value generated by the original function.

Now a predicate of the form:

where random_function(rowid) < 0.1

may be used on any table to return (approximately) 10% of the rows without the function needing to reference any column values or datatypes or locations. The function must be evaluated for every row returned by the select statement because it references a database object (rowid), although the object is not used.

Chrysalis. Received on Thu Jul 03 1997 - 00:00:00 CDT