Re: Index Fast Full Scan vs Index Full Scan?

In article <qbJy7.5$mL2.511_at_nsw.nnrp.telstra.net>, sweidanz_at_yahoo.com says...
>
>I dont totally understand whats the difference between these two access
>methods? I know they both access the index only to get the requested
>records because all the columns specified in the query including the
>predicate clause exist in the index. But the thing that i dont understand
>is the differences and why the index fast full scan does not return the
>data sorted?
>
>Thanks,ZS

the index ffs (fast full scan) reads the index blocks like they were table blocks not like they were index blocks. It uses multi-block IO, reads N blocks of the index at a time (some of which might be leaf nodes with data, some of which might be branch blocks that are normally used to navigate the index). It basically ignores the branch blocks, just reads leaf nodes and returns the rows it finds there. It does not read the index as a data structure, just as a collection of blocks with data.

The index full scan in turn starts at the root node and traverses the index to the first entry and then reads all of the leaf nodes (which are all linked together so once you get to the first, you can get to the last by walking the list) in order. It does not use multi-block IO as the ffs does, it reads the index as a data structure.

You would use the ffs to quickly get all of the data from the index in the most efficient manner.

You would use the fs to quickly get IN ORDER all of the data from the index (but not as fast perhaps as the ffs -- but you get the data sorted)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp