How to Sample Rows with the SAMPLE Clause in Oracle

On a table with hundreds of millions of rows, an exact answer is not always worth the wait. A quick estimate of an average, a distribution, or a row count is often enough, and test data can be a small subset of production. The SAMPLE clause reads a random sample of a table's rows, and SEED makes the sample repeatable.

Code for This Guide

The main examples are in the examples/flashback-sampling folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

select ... from table [sample [block] (percent) [seed (n)]] [alias]

The clause goes right after the table name, before its alias, and applies to that table only.

Sample Rows

The first query counts the rows of a 10% sample of CREW_ASSIGNMENTS; the second estimates the average salary from half of the employees.

Example:

select count(*) as rows_in_10_percent from crew_assignments sample (10) seed (26);

select round(avg(salary)) as sample_avg_salary from employees sample (50) seed (1);

Output:

   ROWS_IN_10_PERCENT
_____________________
                 1160

   SAMPLE_AVG_SALARY
____________________
               15154

The percentage is a probability, not an exact share: each row has a 10% chance of being chosen, so the sample holds about 10% of the rows, not exactly 10%.

Repeatable Samples and Estimates

Without SEED, each run picks different rows. With the same seed, the sample is the same every time, as long as the table has not changed. Scaling a sample's count or sum by the inverse of the percentage estimates the whole table.

Example:

select count(*) as all_rows from crew_assignments;

-- the same seed returns the same sample every time
select count(*) as sample_1 from crew_assignments sample (10) seed (26);
select count(*) as sample_2 from crew_assignments sample (10) seed (26);

-- scale the sample up to estimate the whole table
select count(*) * 10 as estimated_rows from crew_assignments sample (10) seed (26);

Output:

   ALL_ROWS
___________
      11224

   SAMPLE_1
___________
       1160

   SAMPLE_2
___________
       1160

   ESTIMATED_ROWS
_________________
            11600

The estimate of 11,600 is within about 3% of the true 11,224. Averages, ratios, and percentages need no scaling at all, since the sample estimates them directly.

Row Sampling Compared with Block Sampling

SAMPLE BLOCK picks whole data blocks rather than single rows, so Oracle reads only the chosen blocks, which is much faster on a very large table. The price is less randomness: rows stored together, often rows inserted together, are chosen together, which can bias the result. On a table that fits in a few blocks, a block sample can even return no rows at all. Use row sampling for accuracy and block sampling for speed on big tables.

Things to Know

• The sample is taken before WHERE is applied, so a filter reduces the sample further.
• In a join, SAMPLE applies to the one table it follows; sampling both sides of a join usually loses most of the matching rows.
• For a statistically fixed number of rows, rather than a fraction, order by DBMS_RANDOM.VALUE and fetch the first n rows, at the cost of reading the whole table.
• Copying a sample into a new table is a quick way to build test data: create table t as select * from big_table sample (1) seed (42).

Related Guides

• How to Query Past Data with Flashback Query in Oracle
• How to Filter Groups with HAVING in Oracle SQL

Conclusion

The SAMPLE clause reads a random subset of a table, with each row chosen by the percentage you give. Add SEED for a repeatable sample, scale counts and sums by the inverse of the percentage to estimate the whole table, and choose SAMPLE BLOCK when speed on a very large table matters more than strict randomness.