Can one 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

From Oracle 8i, 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;

If you examine the execution plan of a "Sample Table Scan", you should see a step like this:

TABLE ACCESS (SAMPLE) OF 'EMP' (TABLE)

ORDER BY dbms_random.value()

This method orders the data by a random column number. Example:

SQL> SELECT * FROM (SELECT ename
2                   FROM emp
3                  ORDER BY dbms_random.value())
4   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 1:

SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1;