How to get records randomly from the oracle database

Question

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

User · Answer

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

User · Answer

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

User · Answer

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   https   docs oracle com en database oracle oracle-database 19 sqlrf SELECT html GUID-CFA006CA-6FF1-4972-821E-6996142A51C6 https   docs oracle com en database oracle oracle-database 19 sqlrf SELECT html GUID-CFA006CA-6FF1-4972-821E-6996142A51C6  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 lt  1e6   select   from s1  where id gt   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

User · Answer

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  lt  21    Best used for small tables to avoid selecting large chunks of data only to discard most of it

User · Answer

SELECT   FROM table SAMPLE 10  WHERE ROWNUM  lt   20    This is more efficient as it doesn t need to sort the Table

User · Answer

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

User · Answer

SELECT column FROM   SELECT column  dbms random value FROM table ORDER BY 2   where rownum  lt   20

User · Answer

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

User · Answer

SELECT   FROM         SELECT       FROM   table     ORDER BY DBMS RANDOM RANDOM  WHERE  rownum  lt  21

[oracle] How to get records randomly from the oracle database?

We have Teacher table

Oracle Syntax

Examples related to oracle

Examples related to select

Examples related to random