What’s the Difference between a Full Index Scan and a Fast Full Index Scan in Oracle?

• Download Index_FS_and_FFS.zip - 587 B

Introduction

This is an alternative to What’s the Difference between a Full Index Scan and a Fast Full Index Scan in Oracle? The main goal for this alternative is to explain the behaviour of index full scan and index fast full scan in more detail.

Do Index Properties Define the Usage of an Index Scan?

The original tip states that the properties of the index define if it can be used for index full scan or index fast full scan. To be precise, the tip says that full index scan can be used when index is in the order required by the query while fast full index scan would be used when index is in no particular order.

From original tip:

A full index scan is where Oracle reads the data from the index, and the index is in the order required by the query.

...

A fast full index scan is similar to a full index scan. This type of scan happens when the data in the index is in no particular order.

If this would be true, it would mean that there could be indices that are in no order and that the same index could not be used for both index full scan and index fast full scan.

Does Index Scan Mean that the Data is Returned Solely from the Index

The original tip also defines the following:

From original tip:

When you run a SELECT query on a database, Oracle needs to return some data to you. To do this, it can just load the data from the table and display it to you.

However, if there is an index on the table, and it can be used, then Oracle performs what is called an “index scan”.

This allows Oracle to show you the results just by looking at the index, instead of the table itself.

We'll also have a look if an index scan eliminates the need to visit the table.

Create the Test Environment

First, we need some test material. To keep the situation similar to the original tip, let's create a test table:

-- Table definition
CREATE TABLE All_Customers (
   Id          NUMBER        NOT NULL,
   First_Name  VARCHAR2(100) NOT NULL,
   Last_Name   VARCHAR2(100) NOT NULL,
   Description VARCHAR2(100) NULL
);

To generate values into the Id column, let's also create a sequence:

-- Sequence for Id
CREATE SEQUENCE CustomerSequence
   INCREMENT BY 1
   NOORDER
   NOCACHE;

And then fill some data into the table:

-- Add test data
BEGIN
   FOR counter IN 1 .. 10000 LOOP
      INSERT INTO All_Customers (Id, First_Name, Last_Name, Description)
         VALUES (CustomerSequence.NEXTVAL, 
                 DBMS_RANDOM.STRING('A', 10),
                 DBMS_RANDOM.STRING('A', 20), 
                 DBMS_RANDOM.STRING('A', 100)); 
   END LOOP;
END;
/

Since the tip is about using indexes, let's create our test index. Note, only one index will be created.

-- Create the test index
CREATE INDEX X_Customer_Id_Name ON All_Customers (Id, First_Name);

Index Full Scan

Index full scan is a mechanism where Oracle does not read all the required entries from the index by traversing the tree from top to leaf for the rows. Instead, it traverses the tree from top to bottom on "the left side" of the index. When the left-most leaf level is found, it continues to read the index horizontally in the order defined by the index keys, block-by-block.

Consider the following query:

-- Test for INDEX FULL SCAN
SELECT First_Name, Last_Name FROM All_Customers ORDER BY Id, First_Name;

If the explain plan is generated, it would look like this:

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |  9581 |  1094K|   236   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALL_CUSTOMERS      |  9581 |  1094K|   236   (0)| 00:00:03 |
|   2 |   INDEX FULL SCAN           | X_CUSTOMER_ID_NAME |  9581 |       |    39   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Why did the optimizer choose index full scan for the query? The query returns the data in the order defined by the indexed columns, but the query does not define any range. Therefore, with index full scan, all rows need to be returned in correct order.

A simple alternative for this would be a full table scan to read all rows and then sort the data in desired order. However, since the index already defines the correct order, using the already existing order from index saves us multiple CPU cycles the sorting would require.

Now what about the table access? Based on the plan, the table is accessed using ROWID after fetching the row from the index. This happens because not all the required data is present in the index. Since the query fetches Last_Name along with Id and First_Name columns, the Last_Name needs to be fetched from the table.

If the query would not contain the Last_Name column, the situation would be quite different. Let's have a look:

-- Test 2 for INDEX FULL SCAN
SELECT First_Name FROM All_Customers ORDER BY Id, First_Name;

This query produces a plan like this:

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |  9581 |   608K|    39   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | X_CUSTOMER_ID_NAME |  9581 |   608K|    39   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

As you see, we now have no table access. This situation is called a covered query. In other words, the index already contains all the data the query needs so it covers the query. In such situation, there's no point in going to the table anymore.

Note that a covered query can happen with many types of index accesses, not only with index full scan so these are different concepts.

Index Fast Full Scan

Now what about index fast full scan? Consider the following query on the same table.

-- Test for INDEX FAST FULL SCAN
SELECT First_Name FROM All_Customers;

This query produces the following plan:

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |  9581 |   486K|    12   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| X_CUSTOMER_ID_NAME |  9581 |   486K|    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

The first thing to notice is that index fast full scan is used on the same index as previously index full scan was used. So this is just another way to access a single index.

Since data from First_Name is returned and there is no other requirements, reading the data from the index and returning it as-is produces sufficient result.

If the query would contain for example DISTINCT keyword, the situation would be slightly different

-- Test for INDEX FAST FULL SCAN
SELECT DISTINCT First_Name FROM All_Customers;

The plan is:

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |  9581 |   486K|       |   137   (1)| 00:00:02 |
|   1 |  HASH UNIQUE          |                    |  9581 |   486K|   576K|   137   (1)| 00:00:02 |
|   2 |   INDEX FAST FULL SCAN| X_CUSTOMER_ID_NAME |  9581 |   486K|       |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

The difference is that after reading the data from the index, we need an additional step to eliminate duplicates. Elimination is done using a HASH UNIQUE operation.

In short, the main difference between index full scan and index fast full scan is the way the index is read. Both access paths read the whole leaf level but with index fast full scan the leaf level of the index is read in the order the blocks are located on the disk, not in the order of the indexed data as index full scan did.

To Take Home With You...

Few important things covered were:

• Index full scan and index fast full scan are access paths that can be used on the same indexes.
  Also, note that there are multiple other access paths.
• Index is always in order, along with other things the access path decision defines if the order information is used or not
• When all the data required by the query is returned from the index, the index covers the query
• Covering can happen with several access paths

References

Few references you may find useful are listed below:

• Optimizer Access Paths
• DBMS_RANDOM
• EXPLAIN PLAN

History

• 13^th July, 2017: Alternative created