Re: Random Sample Using SQL?

From: Kendall Willets (TV DBA) <"Kendall>
Date: 1997/07/03
Message-ID: <33BC0E59.6350_at_wellsfargo.com>#1/1


Brian Ewins wrote:
>
> Kendall Willets (TV DBA) wrote:
 > How about using a random function of ROWNUM? That would provide you
> > with a unique integer for each row, so you should be able to get a
> > decent pseudo-random sequence out of them.
> >

 >
> 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:

Given any unique key, one should be able to come up with a hash function which maps each value to a "random" hash value. (That's essentially what random_fn does in the more restricted case of integers.)

Since Oracle allows a table to be stored in hashed (cluster) form, where each row is mapped to a "random" hash block, a very fast way to get a random sample would be to pick a hash block and get all the rows in it. Such a fetch would take little i/o and certainly no table scan. The question is how to do such a fetch.

One way would be to pick one row (say the first key value), see where it's stored by looking at its rowid, and fetch all the other rows with rowid's in the same hash bucket. I'm not sure if it's possible to tell by rowid what hash block a row is in, but it's a start.

If anyone knows more about rowid format, please respond!

Kendall Received on Thu Jul 03 1997 - 00:00:00 CEST

Original text of this message