Overview: SELECT SAMPLE

One feature that many developers and data analysts are not aware of is the SAMPLE function in Oracle.

It is quite an old feature (not sure exactly when it was added). Below is a simple example of how it works.

We use a table with 9 000 000 rows and seelct from it with a sample percentage of 0.0001

select *
from qz_results
sample (0.0001)
;

We are basically randomly selecting records from the table qz_results with a 0.0001% probability. What is to be noted is that the result returned by this query is not 0.0001% of the total number of rows (which in our case is 9). For example, frist time I run the query I got a result set of 13 rows:

PLAYER          ROUND      SCORE
---------- ---------- ----------
Williams            3        112
Radetskiy           2        115
Radetskiy           1        108
Radetskiy           3        122
Williams            2        112
Radetskiy           2        115
Williams            3        112
Johansson           2        125
Radetskiy           3        122
Williams            2        112
Johansson           3        105
Williams            3        112
Johansson           3        105

13 rows selected. 

Second time I got 9 rows:

PLAYER          ROUND      SCORE
---------- ---------- ----------
Williams            3        112
Radetskiy           1        108
Radetskiy           2        115
Johansson           1        110
Johansson           2        125
Radetskiy           2        115
Williams            1        120
Radetskiy           1        108
Williams            2        112

9 rows selected. 

Examples created on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0