Difference Between Full Index Scans and Fast Full Index Scan

[shrinika]

Tim, Can you please throw more lights on Difference Between Full Index Scans and Fast Full Index Scan

I read the below link and i am still not very clear...

http://nguyentichthanhblog.blogspot.com ... s-and.html

As per the above link, full index scan read single block read and fast full index scan read multi-block read.

still i don't understand the correct difference.

Any help is appreciated.

[Tim...]

Hi.

The index is made up of blocks. Those blocks can be stored anywhere on the disk, not necessarily next to each other and not necessarily in order.

When Oracle reads the blocks in from disk it has two choices.

1) Read the blocks in any order. Basically, the first block it sees on disk it reads, regardless of where in the index it is. It will eventually read the whole index, but in whatever order it comes. This is like a multi-block read of a table, but it is on the index. This is a fast full index scan.

2) Alternatively, it can traverse the index, starting at the root node and working through the index tree in order. This means it is reading one block at a time like a random I/O on a table. The order is precise, but that affects the time it takes to retrieve the data, since each read is a single operation, looking for a specific block. This is a full index scan.

Cheers

Tim...

[shrinika]

Tim, Nice explanation!!

Here is my another question and answer. Please correct me if my answer is wrong.

When does oracle use fast full index scan?

Code: Select all

Oracle use fast full index scan when all your query columns are on index segment and
you are looking only few number of rows.

when does oracle use full index scan?

Code: Select all

Oracle use fast full index scan when all your query columns are on index segment and
you are looking most of the rows in the index segment.

[Tim...]

Hi.

It's the other way round. The fast full index scan is a replacement for a full table scan, so you should only be doing this when you require most of the rows from the table, but the index can supply all the data.

Cheers

Tim...

[shrinika]

Tim, Yes. you are right!! I mixed up. Thanks again. I got it!!

[Tim...]