The sample clause logically belongs to the table (or more accurately to the query table expression) so that where clauses are applied after determining the sample.
create table tq84_sample (
id integer,
val varchar2(3)
);
insert into tq84_sample
select
level,
dbms_random.string('a', 3)
from
dual connect by level <= 100;
Expect approximately 2 records to be returned:
select * from tq84_sample sample(20) where mod(id, 10) = 0;
Cleaning up:
drop table tq84_sample;
Execution plan
In an execution plan, a sample access to table is identified by a TABLE ACCESS (SAMPLE)row source:
create table tq84_smp (
id number,
val number,
txt varchar2(10)
);
insert into tq84_smp
select
level,
mod(level*37, 11),
to_char(1234+mod(level*197, 193), 'fmxxxxx')
from
dual connect by level <= 1000;
begin
dbms_stats.gather_table_stats(user, 'tq84_smp');
end;
/
explain plan for
select
*
from
tq84_smp sample(9)
where
id < 100
;
select * from table(dbms_xplan.display(format=>'basic rows'));
--
-- ------------------------------------------------
-- | Id | Operation | Name | Rows |
-- ------------------------------------------------
-- | 0 | SELECT STATEMENT | | 9 |
-- | 1 | TABLE ACCESS SAMPLE| TQ84_SMP | 9 |
-- ------------------------------------------------
drop table tq84_smp;
Selecting approximately N records
The sample clause only allows to specify an approximate percentage of the returned records, not an approximate amount of records.
The following example demonstrates how it is possible to select approximately n (here: 1234) records from a table:
create table tq84_t (id integer);
insert into tq84_t
select
dbms_random.value(1, 100000000)
from
dual connect by level <= 1000000;
select
count(*)
from
tq84_t
where
mod(id, trunc((select count(*) / 1234 from tq84_t))) = 0;
drop table tq84_t;
Sampling views using DISTINCT, GROUP BY etc.
select
*
from
dba_objects SAMPLE(1);
--
-- ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
See also
Limiting the resultset of a select statement (fetch first rows only or rows between records n and m).
The sample clause implements SQL feature T613 (but uses sample rather than tablesample).