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.
As the name suggests, Index Full Scans are Index Scans where the entire index is scanned in order. Following are the situations where optimizer chooses Index Full Scans:- A predicate references a column in the index. This column need not be the leading column. No predicate is specified and all columns in the table and…
Index Unique Scans returns at most 1 rowid, this means that whenever the condition in WHERE clause guarantees that only one row would be returned Oracle optimizer chooses Index Unique Scans. To summarize this, Index Unique Scan access path is used when all columns of a unique (B-tree) index or an index created as a…
Oracle Database Internal Mechanism 