index range scan and index full scan

668822

hi masters,

i always think about what is the difference between index range scan and index full scan..


index full scan scans the full index structure that is leaf, block and root.

how does index range scan work internally?? how it is different from index full scan?? when to use index range scan?? which is costly???

i fond some docs on these, but no one discuss internals.. anyone know about any link that j. lewis written about these scans??

will be helpfull

thanks and regards
VD

Jonathan Lewis

vikrant dixit wrote:
i always think about what is the difference between index range scan and index full scan..

Essentially there is no difference.

Based on a starting value, Oracle checks the root block using the partial keys stored there to select which block from the next level down (typical a layer of branch blocks) to go to.

From the relevant branch block it uses the partial keys to identify which block in the next level down to go to (typically a leaf block).

Once it's reached a leaf block, it can find the first relevant key value, then walk through the list of keys jumping to the table. If it gets to the end of the leaf block, it uses the "next pointer" to jump to the next relevant leaf block.

Since the optimizer has enough information to work out a starting value and an ending value for the scan, the run time engine can keep moving from leaf to leaf until it hits the "stop" leaf.

The only difference between the full scan and the range scan is the the full scan descends through the branches to the first leaf of the index, and walks the index to the very last leaf. (In effect, the start value is "minus infinity" and the end value is "plus infinity".)


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk


"Science is more than a body of knowledge; it is a way of thinking" Carl Sagan

153119

Index full scan scans level 0 nodes only.
Index range scan just scans the B-tree.
When you know how a B-tree is traversed (this is basic Information Technology) you also know how index range scan works.
Any textbook on datastructures should be capable to help you out.

-----
Sybrand Bakker
Senior Oracle DBA

668822

thanks for your help.

thanks and regards
VD

Naveed Yameen Khakoo

Hi,

See if the following post by Donald helps:

http://www.oracle-training.cc/oracle_tips_index_access.htm

Regards,
Naveed.

Jonathan Lewis

vikrant dixit wrote:
i always think about what is the difference between index range scan and index full scan..

Essentially there is no difference.

Based on a starting value, Oracle checks the root block using the partial keys stored there to select which block from the next level down (typical a layer of branch blocks) to go to.

From the relevant branch block it uses the partial keys to identify which block in the next level down to go to (typically a leaf block).

Once it's reached a leaf block, it can find the first relevant key value, then walk through the list of keys jumping to the table. If it gets to the end of the leaf block, it uses the "next pointer" to jump to the next relevant leaf block.

Since the optimizer has enough information to work out a starting value and an ending value for the scan, the run time engine can keep moving from leaf to leaf until it hits the "stop" leaf.

The only difference between the full scan and the range scan is the the full scan descends through the branches to the first leaf of the index, and walks the index to the very last leaf. (In effect, the start value is "minus infinity" and the end value is "plus infinity".)


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk


"Science is more than a body of knowledge; it is a way of thinking" Carl Sagan

668822

thanks john,


but is this principle applies to all type of indexes?? like bitmap, cluster, etc.,??

thanks and regards
VD