Performance issue when using ORDER BY dbms_random.value for Oracle database

Question

I need to get 1000 random rows from a table and found solution for Oracle. But if I use this query when retrieving data from table containing large amount of rows it takes up to 3 minutes to complete:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum <= 1000

It happens because all rows are selected and then all of them are ordered by random value when I need only 1000. Is there any workaround for such problem? Maybe using dbms_random.value along with some cursor that will pick random row.

Answer 1

I would do that in this manner:

SELECT column 
FROM table sample (1)
where rownum <= 1000
--ORDER BY dbms_random.value 
 ;

Will get a sample of 1 percent from table, stop at first 1000 (and, if needed, order randomly) .

It is possible to exist a better way to do want you want. This is what I'll try.