CODEWITHSUNDEEP
oracle-databaseselectrandom
Bhadra
Bhadra

Reputation: 1181

How to get records randomly from the Oracle database?

I need to select rows randomly from an Oracle DB.

Ex: Assume a table with 100 rows, how I can randomly return 20 of those records from the entire 100 rows.

Upvotes: 107

Views: 215437

Answers (11)

N3K0100183
N3K0100183

Reputation: 50

Since Oracle 12c we can use the FETCH statement for that

SELECT *
FROM  table
ORDER BY DBMS_RANDOM.RANDOM
FETCH FIRST 20 ROWS ONLY

Upvotes: 0

Nikesh Ajalkar
Nikesh Ajalkar

Reputation: 1

select * from (SELECT * FROM emp ORDER BY DBMS_RANDOM.RANDOM )where
rownum <= 1 ;

Upvotes: -1

M J
M J

Reputation: 1

-- Q. How to find Random 50% records from table ?

when we want percent wise randomly data

SELECT * FROM ( SELECT * FROM table_name ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum <= (select count(*) from table_name) * 50/100;

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

In case of huge tables standard way with sorting by dbms_random.value is not effective because you need to scan whole table and dbms_random.value is pretty slow function and requires context switches. For such cases, there are 3 additional methods:

1: Use sample clause:

for example:

select *
from s1 sample block(1)
order by dbms_random.value
fetch first 1 rows only

ie get 1% of all blocks, then sort them randomly and return just 1 row.

2: if you have an index/primary key on the column with normal distribution, you can get min and max values, get random value in this range and get first row with a value greater or equal than that randomly generated value.

Example:

--big table with 1 mln rows with primary key on ID with normal distribution:
Create table s1(id primary key,padding) as 
   select level, rpad('x',100,'x')
   from dual 
   connect by level<=1e6;

select *
from s1 
where id>=(select 
              dbms_random.value(
                 (select min(id) from s1),
                 (select max(id) from s1) 
              )
           from dual)
order by id
fetch first 1 rows only;

3: get random table block, generate rowid and get row from the table by this rowid:

select * 
from s1
where rowid = (
   select
      DBMS_ROWID.ROWID_CREATE (
         1, 
         objd,
         file#,
         block#,
         1) 
   from    
      (
      select/*+ rule */ file#,block#,objd
      from v$bh b
      where b.objd in (select o.data_object_id from user_objects o where object_name='S1' /* table_name */)
      order by dbms_random.value
      fetch first 1 rows only
      )
);

Upvotes: 8

SMerrill8
SMerrill8

Reputation: 568

Here's how to pick a random sample out of each group:

SELECT GROUPING_COLUMN, 
       MIN (COLUMN_NAME) KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE) 
         AS RANDOM_SAMPLE
FROM TABLE_NAME
GROUP BY GROUPING_COLUMN
ORDER BY GROUPING_COLUMN;

I'm not sure how efficient it is, but if you have a lot of categories and sub-categories, this seems to do the job nicely.

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30848

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

Upvotes: 152

Jinwu Seo
Jinwu Seo

Reputation: 61

In summary, two ways were introduced

1) using order by DBMS_RANDOM.VALUE clause
2) using sample([%]) function

The first way has advantage in 'CORRECTNESS' which means you will never fail get result if it actually exists, while in the second way you may get no result even though it has cases satisfying the query condition since information is reduced during sampling.

The second way has advantage in 'EFFICIENT' which mean you will get result faster and give light load to your database. I was given an warning from DBA that my query using the first way gives loads to the database

You can choose one of two ways according to your interest!

Upvotes: 6

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60282

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.

Upvotes: 70

grokster
grokster

Reputation: 6287

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

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

Upvotes: 15

Nishant Sharma
Nishant Sharma

Reputation: 360

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.

Upvotes: 5

Bishan
Bishan

Reputation: 15710

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

Upvotes: 11

Related Questions