Oracle Database FULL


Example

The FULL hint tells Oracle to perform a full table scan on a specified table, no matter if an index can be used.

create table fullTable(id) as select level from dual connect by level < 100000;
create index idx on fullTable(id);

With no hints, the index is used:

select count(1) from fullTable f where id between 10 and 100;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX  |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

FULL hint forces a full scan:

select /*+ full(f) */ count(1) from fullTable f where id between 10 and 100;
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    13 |    47   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| FULLTABLE |     2 |    26 |    47   (3)| 00:00:01 |
--------------------------------------------------------------------------------