Re: Selecting a random row from a table

Paul,

If your primary key is a sequential number, then getting a random number between 1 and max(key_col) might be better for two reasons:

1. Oracle can determine the value of max(key_col) from the index without scanning the table.
2. The number generated can be used to select the row directly out of the database -- no need to fetch all the rows into an array.

The catch is that there could be gaps in your sequence so that

1. The chance or returning any particular row is not exactly 1/n (where n is the number of rows). This gets worse if there are more gaps or larger gaps.
2. The row you actually select might not exist (if you choose a number in a gap). You'd have to write a loop to select random rows until it finds one.

So, even uglier. But it should save a lot of I/O this way.

Good luck,
Matt

Paul Davies wrote in message <375bebdf_at_newsread3.dircon.co.uk>...
>Well since no one has replied here's my suggestion - which I find rather
>messy:
>
>Use a cursor to select the data into a vararray
>
>Get a random number between 1 and count(*) - using the rand_int function
>from technet.
>
>Get the value of the vararray with an index equal to that of the random
>number
>
>Anyone got any better suggestions??
>
>
>
>
>Paul Davies wrote in message <375939d2_at_newsread3.dircon.co.uk>...
>>I'm wish a select a single row randomly from a table.
>>
>>Can someone tell me how to do this using PL/SQL?
>>
>>Thanks
>>
>>Paul
>>
>>
>
>
Received on Tue Jun 08 1999 - 18:40:52 CDT