Forum Stats

  • 3,816,521 Users
  • 2,259,200 Discussions
  • 7,893,502 Comments

Discussions

index range scan and index full scan

668822
668822 Member Posts: 901
edited Jan 22, 2009 6:55AM in General Database Discussions
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
Tagged:

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,953 Blue Diamond
    Answer ✓
    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

Answers

  • 153119
    153119 Member Posts: 5,173
    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
    668822 Member Posts: 901
    thanks for your help.

    thanks and regards
    VD
  • Hi,

    See if the following post by Donald helps:

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

    Regards,
    Naveed.
    Naveed Yameen Khakoo
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,953 Blue Diamond
    Answer ✓
    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
    668822 Member Posts: 901
    thanks john,


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

    thanks and regards
    VD
This discussion has been closed.