Select a random selection of results from the query result

Question

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

If this does not happen, is there a standard way to get a random selection of results from a set of queries? For example, how can you get 1000 random rows from a query that usually returns millions?

Answer 1

 SELECT * FROM ( SELECT * FROM mytable ORDER BY dbms_random.value ) WHERE rownum <= 1000 

Answer 2

The SAMPLE clause will give you a random sample of the percentage of all rows in the table.

For example, here we get 25% of the lines:

 SELECT * FROM emp SAMPLE(25) 

The following SQL (using one of the analytic functions) will give you a random selection of a certain number of each occurrence of a certain value (similar to GROUP BY) in a table.

Here we select 10 of them:

 SELECT * FROM ( SELECT job, sal, ROW_NUMBER() OVER ( PARTITION BY job ORDER BY job ) SampleCount FROM emp ) WHERE SampleCount <= 10 

Answer 3

This is not an ideal answer, but it will be much better.

 SELECT * FROM ( SELECT * FROM mytable sample (0.01) ORDER BY dbms_random.value ) WHERE rownum <= 1000 

The sample will give you a percentage of your actual table, if you really want 1000 rows, you will need to adjust this number. Most often, I just need an arbitrary number of lines, so I do not limit my results. In my database with 2 million rows, I get 2 seconds versus 60 seconds.

 select * from mytable sample (0.01) 

Answer 4

There is also a special selection suggestion for selecting a percentage: http://oracleact.com/papers/sampleclause.html

Answer 5

 SELECT * FROM TABLE_NAME SAMPLE(1) 

Gives you a share of about 1%, not 1/100 of the number of observations. The likely reason is that Oracle generates a random flag for each observation as to whether to include in the sample that it generates. Argument 1 (1%) in such a generation process plays the role of the probability of choosing each observation in the sample.

If so, the actual distribution of sample sizes will be binomial.

Answer 6

I know that this has already been answered, but having seen so many visits, I would like to add one version that uses the SAMPLE clause, but still allows you to filter the lines first:

 with cte1 as ( select * from t_your_table where your_column = 'ABC' ) select * from cte1 sample (5) 

Please note, however, that the ROWID column is required to select a base, which means that it may not work, for example, for some views.

Answer 7

The Sample function is used for sample data in ORACLE. So you can try the following: -

 SELECT * FROM TABLE_NAME SAMPLE(50); 

Here 50 is the percentage of data contained in the table. Therefore, if you want 1000 lines out of 100000. You can execute a query, for example: -

 SELECT * FROM TABLE_NAME SAMPLE(1); 

Hope this helps you.

Answer 8

Something like this should work:

 SELECT * FROM table_name WHERE primary_key IN (SELECT primary_key FROM ( SELECT primary_key, SYS.DBMS_RANDOM.RANDOM FROM table_name ORDER BY 2 ) WHERE rownum <= 10 ); 

Answer 9

We were given the task to select only two entries from the list of agents. 2 random entries for each agent for a week, etc .... and below is what we got and it works

 with summary as ( Select Dbms_Random.Random As Ran_Number, colmn1, colm2, colm3 Row_Number() Over(Partition By col2 Order By Dbms_Random.Random) As Rank From table1, table2 Where Table1.Id = Table2.Id Order By Dbms_Random.Random Asc) Select tab1.col2, tab1.col4, tab1.col5, From Summary s Where s.Rank <= 2;