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.
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;
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).