[oracle] 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.

This question is related to oracle select random

The answer is


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

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!


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.


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

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


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.


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
      )
);

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.


We have to use some queries which will gives us random column from

table

We have Teacher table

Oracle Syntax

SELECT * FROM   
(
SELECT column_name FROM table_name  
ORDER BY dbms_random.value
)  
WHERE rownum = 1;

For better understanding follow screenshot


Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to select

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Examples related to random

How can I get a random number in Kotlin? scikit-learn random state in splitting dataset Random number between 0 and 1 in python In python, what is the difference between random.uniform() and random.random()? Generate random colors (RGB) Random state (Pseudo-random number) in Scikit learn How does one generate a random number in Apple's Swift language? How to generate a random string of a fixed length in Go? Generate 'n' unique random numbers within a range What does random.sample() method in python do?