Index Fast Full Scan vs Index Full Scan?

[swei...@yahoo.com]

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

[Thomas Kyte]

In article <qbJy7.5$mL2...@nsw.nnrp.telstra.net>, swei...@yahoo.com says...

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 (tk...@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

[Vsevolod Afanassiev]

To do "Index Fast Full Scan", Oracle reads index blocks in
no particular order - simply in the order it can find them
in the datafiles. Probably (not sure) it reads multiple
blocks in a single read, just like during full table scan
(using DB_FILE_MULTIBLOCK_READ_COUNT - for example, on Sun
platform it can read 1 MB in a single read). For "normal" index reads
Oracle starts from the root block, then goes to branches, and then to the
leaf blocks - this way it can return results in sorted order.

I don't know what is "Index Full Scan" - where did you find it?

Regards,
Sev

vafan...@aapt.com.au

swei...@yahoo.com wrote in message news:<qbJy7.5$mL2...@nsw.nnrp.telstra.net>...