While going through a thread in Oracle Forums, I came across ‘SAMPLE’ clause which is used in SELECT statement to fetch a specified percentage of rows from the full table. I tried to found out, it is there since Oracle 8i.
FROM customers SAMPLE(1);
In the example statement, Oracle is instructed to randomly visit 1% of the rows in the table.
This clause works for single table queries only. If you include the sample clause with a multiple table query, you will get a parse error or ORA-30561: SAMPLE option not allowed in statement with multiple table references.
There is one more option with this, SAMPLE BLOCK, which fetches specified number of table blocks rather than rows.
FROM customers SAMPLE BLOCK (10) ;
In the example statement, Oracle is instructed to randomly visit 10% of blocks.