ORDER BY dbms_random.value()
This method orders the data by a random column number.
Example: SQL> SELECT * FROM (SELECT ename FROM emp ORDER BY dbms_random.value()) WHERE rownum <= 3;
ENAME
----------
WARD
MILLER
TURNER
The ORA_HASH() function
The following example retrieves a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then returning the data from bucket
SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1;
select a random collection of rows from a table
The following methods can be used to select a random collection of rows from a table:
The SAMPLE Clause
The easiest way to randomly select rows from a table is to use the SAMPLE clause with a SELECT statement.
Examples: SELECT * FROM emp SAMPLE(10);
In the above example, Oracle is instructed to randomly return 10% of the rows in the table.
SELECT * FROM emp SAMPLE(5) BLOCKS;
This example will sample 5% of all formatted database blocks instead of rows.
This clause only works for single table queries on local tables. If you include the SAMPLE clause within a multi-table or remote query, you will get a parse error or "ORA-30561: SAMPLE option not allowed in statement with multiple table references". One way around this is to create an inline view on the driving table of the query with the SAMPLE clause.
Example: SELECT t1.dept, t2.emp FROM (SELECT * FROM dept SAMPLE(5)) t1,emp t2 WHERE t1.dep_id = t2.dep_id;