Select a random sample of results from a query result

user_56672 0
Viewed 214043

This question asks about getting a random(ish) sample of records on SQL Server and the answer was to use TABLESAMPLE. Is there an equivalent in Oracle 10?

If there isn't, is there a standard way to get a random sample of results from a query set? For example how can one get 1,000 random rows from a query that will return millions normally?

random oracle sql
9 Answers
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        ORDER BY
                dbms_random.value
        )
WHERE rownum <= 1000
user_551590

Suppose you are trying to select exactly 1,000 random rows from a table called my_table. This is one way to do it:

select
    *
from
    (
        select
            row_number() over(order by dbms_random.value) as random_id,
            x.*
        from
            my_table x
    )
where
    random_id <= 1000
;

This is a slight deviation from the answer posted by @Quassnoi. They both have the same costs and execution times. The only difference is that you can select the random number used to fetch the sample.

user_97958170
Related