Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
index range scan and index full scan
668822
Member Posts: 901
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
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
-
vikrant dixit wrote:Essentially there is no difference.
i always think about what is the difference between index range scan and index full scan..
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
-
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 -
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.
-
vikrant dixit wrote:Essentially there is no difference.
i always think about what is the difference between index range scan and index full scan..
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 -
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.