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 Oracle version(s): 8.1.6, 8.1.7, 9.0.1 |
|
How do I randomly select rows from a table? |
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 |
|
|
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
