Making Use of INDEX FFS

What is Index FFS?

In Oracle there are some SQL queries that can be resolved by reading the index without touching the table data. 
INDEX FAST FULL SCAN is the equivalent of a FULL TABLE SCAN, but for an index.  
It reads using multiblock reads, but results are NOT returned sorted. 
For a query to make use of Index FFS the column should be defined as NOT NULL 
or at least one column in a composite index is NOT NULL.

Create the table

SQL> create table mytab as select * from user_objects;

Table created.

SQL> create unique index mytab_id on mytab(object_id);

Index created.

SQL> set autotrace ON
SQL> select object_id from mytab;

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   745 |  9685 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTAB |   745 |  9685 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Analyze the table


SQL> analyze table mytab compute statistics;

Table analyzed.

SQL> select object_id from mytab;

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   745 |  2980 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTAB |   745 |  2980 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Define NOT NULL for the column


SQL> alter table mytab modify(object_id not null);

Table altered.

SQL> select object_id from mytab;

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   745 |  2980 |     2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| MYTAB_ID |   745 |  2980 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

When the column is defined as NOT NULL Oracle knows for sure that all column values are available in the index.

Optimizer behaviour for automatic type conversion

An index unique scan or index range scan is faster than Index FFS.
The following query should use index unique scan

select object_id from mytab where object_id=1;

Here the data type for object_id is number. 
Oracle also does automatic type coversion between number and varchar2.
But the following query makes use of index FFS instead of unique scan.

select object_id from mytab where object_id='1';

Modify the query to use Index FFS


NULL values are not included while index creation. 

If the where clause of the query explicitly contains "is not NULL" clause 
then the resultset does not contain NULL values.
In this case INDEX FAST FULL SCAN is possible.

Modify the table to remove NOT NULL constraint

SQL> alter table mytab modify(object_id NULL);

Table altered.


SQL> set autotrace ON
SQL> select object_id from mytab where object_id is not null;

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   745 |  2980 |     2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| MYTAB_ID |   745 |  2980 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------


SQL> drop table mytab;

Table dropped.