The Oracle (tm) Users' Co-Operative FAQ

How do I randomly select rows from a table?


Author's name:John Darrah

Author's Email: john.Darrah@usa.net

Date written: 7th Aug 2001
Updated:  3rd May 2004

Oracle version(s): 8.1.6, 8.1.7, 9.0.1

How do I randomly select rows from a table?

Back to index of questions


In Oracle 8i and 9i, the easiest way to randomly select data out of a table is to use the SAMPLE clause with a select statement.

Example:

               SELECT emp
               FROM emp
               SAMPLE(10);

In the example statement, Oracle is instructed to randomly visit 10% of the rows in the table.

The restriction to this clause is that it works for single table queries only. If you include the sample clause with a multiple table 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

This question is addressed by the following documents:

Author

Title/URL

Suggested by

Referee's comments

 

http://www.revealnet.com/newsletter-v2/randomising.htm

Diego Gallicchio

 

I was looking over the FAQ’s.  I noticed that you have a question on randomly selecting rows.  I looked this issue up a long time ago and came across this article. I have used this technique in an application with over 100K rows.  I have to admit that I do not fully understand how the author is getting the random records, but I can attest to the fact that it works.


Further reading: N/A


Back to top

Back to index of questions