Hacker News Reader

Comment by praptak | Hacker News Reader

Yup, and with single-column indexes on both of the columns it will try to pick one with better selectivity.

>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.

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).

There are MANY factors in play. Stats, parameters, histograms... it really depends.

>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.

Seems 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.

Out 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?

Mostly 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.

Reply on news.ycombinator.com