Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 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
- 157 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
- 389 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
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Index Range scan cost ?

448778
Member Posts: 447 Red Ribbon
Why the effective index selectivity of index range scan is calculated this way
(highendval -lowendval)/(highvalue -lowvalue)+ 2/num_distinct ?why its not 1/num_distinct which is used in case of equality predicate?
Second question is why the effective index selectivity calculation for a composite index is calculated differently if there is range scan on the leading column of the indexes and though there are predicates available in the where clause for other column in the index why the cost of those predicates is not included in the index range scan.
e.g I have composite index on (n1,n2,n3) and the where clause is
n1 between 6 and 9 and
n2=3 and
n3='a'
why only the effective index selectivity of the index range scan for the predicate n1 is used in finding effective Index selectivity , but all n1,n2 and n3 are considered in getting effective table selectivity(which makes sense)
P.S:- I am refering Cost based fundamental by Jonathan Lewis.
Edited by: user445775 on Dec 28, 2008 11:06 AM
(highendval -lowendval)/(highvalue -lowvalue)+ 2/num_distinct ?why its not 1/num_distinct which is used in case of equality predicate?
Second question is why the effective index selectivity calculation for a composite index is calculated differently if there is range scan on the leading column of the indexes and though there are predicates available in the where clause for other column in the index why the cost of those predicates is not included in the index range scan.
e.g I have composite index on (n1,n2,n3) and the where clause is
n1 between 6 and 9 and
n2=3 and
n3='a'
why only the effective index selectivity of the index range scan for the predicate n1 is used in finding effective Index selectivity , but all n1,n2 and n3 are considered in getting effective table selectivity(which makes sense)
P.S:- I am refering Cost based fundamental by Jonathan Lewis.
Edited by: user445775 on Dec 28, 2008 11:06 AM
Answers
-
user445775 wrote:Not sure what you intend to say here? You're referring to the selectivity formula for ranges (but I think there is an error, it's not "... + 2 / num_distinct", this part depends on whether it's a closed or open range) and there is nothing special in case of an index range scan.
Why the effective index selectivity of index range scan is calculated this way
(highendval -lowendval)/(highvalue -lowvalue)+ 2/num_distinct ?why its not 1/num_distinct which is used in case of equality predicate?Second question is why the effective index selectivity calculation for a composite index is calculated differently if there is range scan on the leading column of the indexes and though there are predicates available in the where clause for other column in the index why the cost of those predicates is not included in the index range scan.Because the remaining predicates can only be applied while performing the range scan on the leaf blocks, so for the effective index selectivity (the number of leaf blocks to visit) you can only use the predicates up to the range scan (or up to a column omission), but once you read the index leaf blocks you can apply all remaining predicates that can be satisfied by the index (the index selectivity with filters or effective table selectivity).
e.g I have composite index on (n1,n2,n3) and the where clause is
n1 between 6 and 9 and
n2=3 and
n3='a'
why only the effective index selectivity of the index range scan for the predicate n1 is used in finding effective Index selectivity , but all n1,n2 and n3 are considered in getting effective table selectivity(which makes sense)
By the way, this is explained on page 74.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ -
Thanks Randolf,
If you look at the page 72 the index selectivity with predicate n2 between 1 and 3 which is bounded and closed range predicate.
The effective index selectivity is been calculated as
(3-1)/(19-1) +1/20+1/20 (20 is the num_distinct key value for the column n2 in the index).
(Please disregard this question as I have got the answer)
Still not able to understand the 2nd question.
Also regarding second question if the range of n1 is between 1 and 3, will Oracle will only search the leaf blocks which has the value for n1 between 1 and 3 ,the values for other predicates may be or may not belongs to same leaf blocks where Oracle storescolumn value n1 between 1 and 3 , does it mean or it will search the whole index and then after reading all the leaf blocks it will apply all the predicate filter?
Edited by: user445775 on Dec 28, 2008 3:43 PM -
user445775 wrote:It will scan all leaf blocks that correspond to all predicates up to the range scan (that's the effective index selectivity), and all predicates that can only be satisfied by checking these leaf blocks will then be applied, that's the effective index selectivity with filters, because these remaining predicates can't be used to restrict the number of leaf blocks to examine, but can then only be applied as filter to these index leaf blocks.
Still not able to understand the 2nd question.
Also regarding second question if the range of n1 is between 1 and 3, will Oracle will only search the leaf blocks which has the value for n1 between 1 and 3 ,the values for other predicates may be or may not belongs to same leaf blocks where Oracle storescolumn value n1 between 1 and 3 , does it mean or it will search the whole index and then after reading all the leaf blocks it will apply all the predicate filter?
This doesn't mean that the whole index and all of its leaf blocks need to be read, it's just the leaf blocks that need to be scanned according to the predicates up to the range scan.
Note that there is a third selectivity, if you include the table visit: You might have predicates that can only be applied to the table after visiting (and filtering) the index leaf blocks (page 78).
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
This discussion has been closed.