Optimizer Access Paths : Fast Full Scan

An index fast full scan reads the entire index as it is i.e. as it exists on the disk. This index read is unsorted.

This access path usage multiblock IO to perform the index scan.

Index fast full scan simply reads all of the leaf, branch and root block of index quickly using multiblock IO and then it ignores the root & branch block and process the leaf block’s data which is unordered.

This scan occurs usually when all columns of the query is available in the index itself and can be severed using index only. It does not go to scan the table after scanning index. Optimizer treat index in this case as skinny version of the table

Lets understand this with a example. We will use few queries on emp table to understand this better.

Query-01: select count(1), job from emp group by job;
Index: create index idx_emp_job on emp(job)

The explain plan is itself explanatory.

Pre-Index Plan
Post-Index Plan

We can see that there are no differences in the plan generated in both scenarios. It is because this is accessing columns more than what index holds.

Query-02: select count(job) from emp;
Index: create index idx_emp_job on emp(job)

The explain plan is itself explanatory. With this query, since the count will be served from the index itself and there is no need to go to the table for any other info, a Fast full scan is used as the access path for this query.

Pre-Index Plan
Post-Index Plan

This explains the FFS optimizer access path.

Leave a Reply

Your email address will not be published.