Full Table Scan (FTS) in Oracle
2 min readA full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria. Just because during accessing large number of blocks with FTS Oracle can use multiblock I/O read call, FTS is sometimes better than index range scans.
| Reason | Explanation |
| No index exists. | If no index exists, then the optimizer uses a full table scan. |
| The query is Unselective. | If the optimizer determines that the query requires most of the blocks in the table, then it uses a full table scan, even though indexes are available. Full table scans can use larger I/O calls. Making fewer large I/O calls is cheaper than making many smaller calls. |
| The table statistics are stale. | For example, a table was small, but now has grown large. If the table statistics are stale and do not reflect the current size of the table, then the optimizer does not know that an index is now most efficient than a full table scan. |
| The table is small | If a table contains fewer than n blocks under the high water mark, where n equals the setting for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, then a full table scan may be cheaper than an index range scan. The scan may be less expensive regardless of the fraction of tables being accessed or indexes present. |
| The table has a high degree of parallelism. | A high degree of parallelism for a table skews the optimizer toward full tablescans ov er range scans. Query the value in the ALL_TABLES.DEGREE column to determine the degree of parallelism |
How a Full Table Scan Works? In a full table scan, the database sequentially reads every formatted block under the high water mark. The database reads each block only once. The following plan was retrieved using the DBMS_XPLAN.DISPLAY_CURSOR function. Because no index exists on the salary column, the optimizer cannot use an index range scan, and so uses a full table scan
SQL> SELECT salary FROM hr.employees WHERE salary > 4000;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL_ID 54c20f3udfnws, child number 0
-------------------------------------
select salary from hr.employees where salary > 4000
Plan hash value: 3476115102
---------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 3 (100)| |
|* 1| TABLE ACCESS FULL| EMPLOYEES | 98 | 6762 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
