Oracle Database - Sample SQL Clause for Table Scans/Access

A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views.

This access path is used when a statement's FROM clause includes:

• the SAMPLE clause
• or the SAMPLE BLOCK clause.

This clause is used for instance to gathered the table statistics. The estimate percent is the number of the sample clause.

To perform a sample table scan when sampling by rows with the SAMPLE clause, Oracle reads a specified percentage of rows in the table. To perform a sample table scan when sampling by blocks with the SAMPLE BLOCK clause, Oracle reads a specified percentage of table blocks.

Example below uses a sample table scan to access 1% of the employees table, sampling by blocks.

SELECT * FROM employees SAMPLE BLOCK (1); 

The Oracle Database - Explain Plan output for this statement might look like this:

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    68 |     3  (34)|
|   1 |  TABLE ACCESS SAMPLE | EMPLOYEES   |     1 |    68 |     3  (34)|
-------------------------------------------------------------------------