Small SAMPLE bug in Oracle 9i (ORA-30561)
If you’re using the SAMPLE clause in Oracle 9i, in combination with a join, be aware of this small gotcha. I found a workaround, thankfully.
select * from (select * from dual), (select * from dual sample (10));
Expected result: should return zero or one row (more or less at random)
Actual result (tested with 126.96.36.199.0): “ORA-30561: SAMPLE option not allowed in statement with multiple table references”
Workaround: put the query with the SAMPLE clause first, i.e.
select * from (select * from dual sample (10)), (select * from dual);
Note: It works fine in 10g (tested under 10.1.0.2.0) since 10g does not restrict how many SAMPLE clauses are in a query.