Oracle full table scan and its execution plan (full table scan)

Full table scan is a common method for accessing database tables in Oracle. As soon as you see the full table scan in the SQL statement execution plan, you have to consider repairing it. The existence of full table scan does have room for optimization. But in fact, full table scan is not the most inefficient in many cases. It depends entirely on different situations and occasions. Either method has advantages and disadvantages, that is, the specific situation should be analyzed. This article describes what full table scan is and when full table scan occurs, and when full table scan is inefficient.

Links involved in this article:

High Water Level line and full table Scan
Enable AUTOTRACE
Common Oracle test table BIG_TABLE
Oracle db_file_mulitblock_read_count Parameter

1. What is full table scan?
Full table scan is to scan all rows in the table. In fact, it is to scan all data blocks in the table, because the smallest storage unit in Oracle is the Oracle block.
Scanning all data blocks includes data blocks within the high water level line. Even empty data blocks are scanned without being released, resulting in an increase in I/O.
During full table scan, these adjacent data blocks are generally accessed in a sequential manner so that I/O can read multiple data blocks at a time.
Reading more data blocks at a time helps to scan the entire table with less I/O. For readable data blocks, the DB_FILE_MULTIBLOCK_READ_COUNT parameter is restricted.

2. When will a full table scan occur?
A. Table index failure or unavailability (for example, using functions, calculation, NULL value, unequal operators, and type conversion for predicates)
B. the query condition returns most of the data in the entire table.
C. Using parallel access to tables
D. Use the full prompt
E. When the statistical information is missing, Oracle considers full table scan to be more efficient than index scan.
F. If the data block in the table is smaller than the value of DB_FILE_MULTIBLOCK_READ_COUNT, full table scan may occur.
3. demonstrate full table Scan

A. Prepare the demo Environment
Scott @ ORA11G> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

-- Create Table t
Scott @ ORA11G> create table t
2
3 SELECT rownum AS n, rpad ('*', 100, '*') AS pad
4 FROM dual
5 connect by level <= 1000;

Table created.

-- Add an index
Scott @ ORA11G> create unique index t_pk on t (n );

Index created.

Scott @ ORA11G> alter table t add constraint t_pk primary key (n) using index t_pk;

Table altered.

-- Collect statistics
Scott @ ORA11G> execute dbms_stats.gather_table_stats ('Scott ', 't', cascade => true );

PL/SQL procedure successfully completed.

Scott @ ORA11G> set autot trace exp;
Scott @ ORA11G> select count (*) from t; ---> count (*) uses an index for fast scanning.

Execution Plan
----------------------------------------------------------
Plan hash value: 454320086
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
----------------------------------------------------------------------
| 0 | select statement | 1 | 2 (0) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 | index fast full scan | T_PK | 1000 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------

Scott @ ORA11G> set autot off;
Scott @ ORA11G> alter table t move; ---> move table

Table altered.

--> The index becomes invalid after moving, as shown below:
Scott @ ORA11G> @ idx_info
Enter value for owner: scott
Enter value for table_name: t

Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
--------------------------------------------------------------------------------
T T_PK N 1 UNUSABLE NORMAL ASC

B. Full table scan due to index failure
Scott @ ORA11G> set autot trace exp;
Scott @ ORA11G> select count (*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 7 (0) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 | table access full | T | 1000 | 7 (0) | 00:00:01 |
-------------------------------------------------------------------

Scott @ ORA11G> set autot off;
Scott @ ORA11G> alter index t_pk rebuild; --> rebuild index

Index altered.

Scott @ ORA11G> @ idx_info
Enter value for owner: scott
Enter value for table_name: t

Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
-----------------------------------------------------------------------------------
T T_PK N 1 VALID NORMAL ASC

C. A full table scan is used to return most of the data in the entire table.
Scott @ ORA11G> select count (pad) from t where n <= 990;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
---------------------------------------------------------------------------
| 0 | select statement | 1 | 105 | 7 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 105 |
| * 2 | table access full | T | 991 | 101K | 7 (0) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("N" <= 990)

-- Index scanning is used when a small part of data is returned.
Scott @ ORA11G> select count (pad) from t where n <= 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 4270555908
Bytes -------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------
| 0 | select statement | 1 | 105 | 3 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 105 |
| 2 | table access by index rowid | T | 10 | 1050 | 3 (0) | 00:00:01 |
| * 3 | index range scan | T_PK | 10 | 2 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("N" <= 10)

D. Full table scan is used to access tables in parallel mode.
Scott @ ORA11G> select/* + parallel (3) */count (pad) from t where n <= 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3126468333
Bytes ----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes ----------------------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 105 | 3 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 105 |
| 2 | px coordinator |
| 3 | px send qc (RANDOM) |: TQ10000 | 1 | 105 | Q1, 00 | P-> S | QC (RAND) |
| 4 | sort aggregate | 1 | 105 | Q1, 00 | PCWP |
| 5 | px block iterator | 10 | 1050 | 3 (0) | 00:00:01 | Q1, 00 | PCWC |
| * 6 | table access full | T | 10 | 1050 | 3 (0) | 00:00:01 | Q1, 00 | PCWP |
Bytes ----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
6-filter ("N" <= 10)
Note
-----
-Degree of Parallelism is 3 because of hint
-- Author: Robinson
-- Blog: http://blog.csdn.net/robinson_0612

E. full table scan is used when the full prompt is used.
Scott @ ORA11G> select/* + full (t) */count (pad) from t where n <= 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
---------------------------------------------------------------------------
| 0 | select statement | 1 | 105 | 7 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 105 |
| * 2 | table access full | T | 10 | 1050 | 7 (0) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("N" <= 10)

F. Full table scan due to missing statistics
Scott @ ORA11G> exec dbms_stats.delete_table_stats ('Scott ', 'T ');

PL/SQL procedure successfully completed.

Scott @ ORA11G> select count (pad) from t where n <= 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
---------------------------------------------------------------------------
| 0 | select statement | 1 | 65 | 7 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 65 |
| * 2 | table access full | T | 10 | 650 | 7 (0) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("N" <= 10)
Note
-----
-Dynamic sampling used for this statement (level = 2)

-- The preceding Execution Plan uses a full table scan and prompts that dynamic sampling is used, that is, the lack of statistical information.
-- If the data block in the table is smaller than the value of DB_FILE_MULTIBLOCK_READ_COUNT, full table scan may occur.

1 2 3 Next Page