Oracle Database - Sample SQL Clause for Table Scans/Access

Card Puncher Data Processing


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

Discover More
Card Puncher Data Processing
Oracle Database - Explain Plan

EXPLAIN PLAN is an Oracle SQL Command that tell you what the query plan for a given SQL would be : if executed it right now in the current session with the current settings For this purpose, it...
Oracle Database Sql Processing
SQL Engine - (Access|Scan) (Paths|Method)

Access paths are relational operator (ways, techniques) that retrieves data from the database. scan In general, index access paths should be used for statements that retrieve a small subset of table...

Share this page:
Follow us:
Task Runner