In Oracle Database 19c, when executing a SQL query, the Oracle Optimizer determines the most efficient way to retrieve the requested data. This method is called an access path.
One of the most common access paths is the Full Table Scan (FTS). In this method, Oracle reads all blocks of a table sequentially to find the rows that satisfy the query condition.
1. What is a Full Table Scan?
A Full Table Scan occurs when Oracle reads every data block of a table, from the first block to the last, searching for rows that match the query condition.
Instead of using an index to locate rows directly, Oracle scans the entire table.
2. How much multi Block to read once?
When Oracle scans a table, it can read multiple data blocks at once instead of reading them one by one.
DB_FILE_MULTIBLOCK_READ_COUNT defines: The maximum number of blocks Oracle reads in one I/O operation during sequential scans.
Example query:
select salary from employees;
3. How to display the PLAN?
Execution plan example:
SQL> EXPLAIN PLAN SET statement_id = 'wadhah_sql_full_table_scan' FOR select salary from employees;
Explained.
SQL>
Resulat:
SQL> select PLAN_TABLE_OUTPUT from TABLE(DBMS_XPLAN.DISPLAY(null,'wadhah_sql_full_table_scan'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 428 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 428 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.
SQL>
The operation TABLE ACCESS FULL indicates that Oracle performed a Full Table Scan.
4. How Full Table Scan Works
When performing a Full Table Scan, Oracle:
- Reads the High Water Mark (HWM) of the table.
- Scans all data blocks up to the HWM.
- Evaluates the WHERE condition on each row.
Oracle often uses multiblock reads, meaning it reads multiple blocks in one I/O operation, which improves performance.
5. When Oracle Chooses Full Table Scan
When a query retrieves a large percentage of the table rows, using an index may require many random reads.
- Small Tables
- Large Data Retrieval
- Missing or Unusable Index
- Parallel Query
6. Advantages of Full Table Scan
- Efficient for small tables
- Good for queries returning many rows
- Supports parallel execution
- Uses multiblock I/O for faster reads
7. Disadvantages
- Reads all table blocks
- Inefficient for highly selective queries
- Can increase I/O load
Google+
Facebook
