Index Fast Full Scans are similar to Index Full Scans i.e they read index blocks in unsorted order, as they exist on disk.This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.
SQL_ID fu0k5nvx7sftm, child number 0 ------------------------------------- select /*+ index_ffs(departments dept_id_pk) */ count(*) from departments Plan hash value: 3940160378 -------------------------------------------------------------------------- | Id | Operation | Name | Rows |Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------
Pingback: db file scattered read – oracle wait event | Oracle Database Internal Mechanism