How to get records randomly from the oracle databa

2019-01-06 13:27发布

问题:

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

回答1:

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


回答2:

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.



回答3:

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

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



回答4:

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


回答5:

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.