Difference between revisions of "Sampling a Table"

Latest revision as of 21:25, 19 April 2016

Getting a few random rows from as Oracle Database table can be done in many different ways.

This will just select the first ten rows Oracle comes across in the table:

select *
from iso_country
where
rownum<=10

They are likely to all be inserted at a similar time and therefore all similar. If you need a cross section:

select *
from iso_country
sample (10) -- percentage of rows to display

The percentage of rows is not precise, there are 249 rows in the sample iso_country table, but the 10% sample count has given a range from 15 - 31 rows:

select count(*)
from iso_country
sample (10) -- percentage of rows to display

Additional conditions can be added to a sampling query, but they appear to be applied after the original sample has been taken, so there is no guarantee that the query will show any rows:

select *
from iso_country
sample (10)
where code like 'A%'

There are many countries in the table that start with A, so the sample must be taken before the criterion is applied.