Difference Between Full Index Scans and Fast Full Index Scan

All posts relating to Oracle database administration.

Moderator: Tim...

Difference Between Full Index Scans and Fast Full Index Scan

Postby shrinika » Thu Aug 02, 2012 3:15 am

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.
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Difference Between Full Index Scans and Fast Full Index

Postby Tim... » Thu Aug 02, 2012 6:43 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Difference Between Full Index Scans and Fast Full Index

Postby shrinika » Thu Aug 02, 2012 2:59 pm

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.
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Difference Between Full Index Scans and Fast Full Index

Postby Tim... » Fri Aug 03, 2012 8:38 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Difference Between Full Index Scans and Fast Full Index

Postby shrinika » Tue Aug 07, 2012 4:20 pm

Tim, Yes. you are right!! I mixed up. Thanks again. I got it!!
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: Difference Between Full Index Scans and Fast Full Index

Postby Tim... » Tue Aug 07, 2012 4:21 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron