4 ms·
>Yup, and with single-column indexes on both of the columns it will try to pick one with better selectivity If the selectivity yields an estimated result more
by ergo98 17y ago
>Yup, and with single-column indexes on both of the columns it will try to pick one with better selectivity
If the selectivity yields an estimated result more than -- in most cases -- about 2% of the data, the database system will not use the index.
- kevingadd 17y agoOut of curiosity, where did you get that number? It's a very interesting, informative factoid. Is it in the Oracle documentation, or is it based on observation/testing?
- ergo98 17y agoMostly from observation of systems in the field, though it is a very hard to gauge number. I had one install where the I/O rate of the disk subsystem was dramatically faster than the query plan was estimating (it was going too far to avoid I/O over forward read speed) and I discovered that there was absolutely no way to control its setting globally, instead having to add forced-index hints.
- Femur 17y agoThat is not necessarily true. The 'last ditch' operation oracle will perform before a full-table scan is an index range-scan (very IO intensive compared with common index use). There are MANY factors in play. Stats, parameters, histograms... it really depends.
- ergo98 17y ago>That is not necessarily true. The 'last ditch' operation oracle will perform before a full-table scan is an index range-scan (very IO intensive compared with common index use). Only if the index is covering.
- praptak 17y agoSeems rational - with enough records per page fetching 2% of the records might mean fetching >90% of the pages, which of course doesn't really improve over full scan.