Re: using index

What about ' is null ' and ' is not null '? Will it have the same behaviour as ' =" " ' and ' <>" " ' ?

Eric Miner (eric_miner_at_peoplesoft.com) wrote:
: Hello,
:
: No, it's not always true that a not equals predicate will result in an index
: not being used. Since you sent this message to three different forums it's
: hard to tell what RDBMS you're using. As for Sybase and MS SQL such a
: predicate will result in a heuristic (aka MAgic Number) being used to cost
: the predicate instead of a selectivity value based on the statistics. As of
: Sybase 11.0.2 (not sure about this, could be 11.0.3) the value of that
: heuristic is 0.90. That is, it will expect 90% of the column to quality for
: the predicate. Now, this may sound high, but it's reasonable in the majority
: of cases. In previous versions of Sybase and in MSSQL 6.5 the value is 0.33
: (33%). This is not as reasonable and resulted in inefficient indexes being
: used. There are cases where an index who's selectivity is estimated at 90% is
: more efficient than a table scan. So, the bottom line answer is, no a not
: equals does not disqualify an index. In MSSQL 7.0 we are no longer able to
: see the selectivity values that are being estimated (they took 302 away) so
: I'm not sure if the heuristic is still in place, but I strongly suspect it
: is. I'm not sure how Oracle rules based will handle this, I suspect they have
: a rule for the predicate. In their cost based mode I suspect they also use a
: heuristic.
:
: Later
: Eric
:
: Ng K C Paul wrote:
:
: > Is is always true that in the where clause column with Not Equal(<>) will
: > not use index?
:
:
:
Received on Sat Oct 10 1998 - 00:00:00 CDT