Re: A question about Randomness in Oracle?

From: Justin Cave <jcave_at_ddbcinc.com>
Date: 18 Mar 2004 21:03:52 -0800
Message-ID: <c83193c7.0403182103.7de3223e@posting.google.com>

Paul <paul_at_not.a.chance.ie> wrote in message news:<MPG.1ac40525478d32c298990d_at_news1.eircom.net>...
>
> Basically, what I have is a certain number of records, each with an
> integer (sequence), and I want to be able to do something like
>
> SELECT * FROM My_Table (RANDOM x 1000) ORDER BY My_Sequence
>
> or whatever the syntax is in Oracle. I want 1000 RANDOM records - i.e.
> if I do this tomorrow or the next day, I will get a completely different
> set of records. Is there an easy way to do this in Oracle?

A couple of options. First, and faster, you can do

SELECT * FROM my_table SAMPLE(10) ORDER BY my_sequence

This will take (approximately) 10% of the rows in my_table and return them ordered by the my_sequence. Oracle picks 10% of the blocks from the table, so getting the sample is relatively fast, but you have an issue in that the number of rows will vary between runs. The bigger the table, the smaller the variance will tend to be.

Your other option is to do

SELECT *
  FROM (SELECT *

         FROM (SELECT * FROM my_table ORDER BY dbms_random.random)
        WHERE rownum <= 1000)

 ORDER BY my_sequence;

This will fetch every row from the table, order them by a random number, take the first 1000 rows, and re-order them by the sequence. Obviously, this is a lot more work for the database, particularly the first step where you have to generate a bunch of random numbers and sort every row in the table.

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC Received on Thu Mar 18 2004 - 23:03:52 CST