How to get records randomly from the oracle databa

Question

I need to select rows randomly. Ex : Let us assume, a table consists with 100 records ,but i need to get only 20 records from out of those 100 records and record selection will be in randomly.. how would i come out from it? I am using oracle as my db. any suggestion would help me greatly. Thanks in advance..

Answer 1

To randomly select 20 rows I think you'd be better off selecting the lot of them randomly ordered and selecting the first 20 of that set.

Something like:

Select *
  from (select *
          from table
         order by dbms_random.value) -- you can also use DBMS_RANDOM.RANDOM
 where rownum < 21;

Best used for small tables to avoid selecting large chunks of data only to discard most of it.

Answer 2

SELECT * FROM table SAMPLE(10) WHERE ROWNUM <= 20;

This is more efficient as it doesn't need to sort the Table.

Answer 3

SELECT column FROM
( SELECT column, dbms_random.value FROM table ORDER BY 2 )
where rownum <= 20;

Answer 4

SAMPLE() is not guaranteed to give you exactly 20 rows, but might be suitable (and may perform significantly better than a full query + sort-by-random for large tables):

SELECT *
FROM   table SAMPLE(20);

Note: the 20 here is an approximate percentage, not the number of rows desired. In this case, since you have 100 rows, to get approximately 20 rows you ask for a 20% sample.

Answer 5

SELECT *
FROM   (
    SELECT *
    FROM   table
    ORDER BY DBMS_RANDOM.VALUE)
WHERE  rownum < 21;