Oracle Database Hints 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 |
--------------------------------------------------------------------------------
