From: paulkcng@news.netvigator.com (Ng K C Paul)
Subject: Re: using index
Date: 1998/10/10
Message-ID: <6vmlof$it2$1@imsp009a.netvigator.com>#1/1
References: <6vjqth$7dg$1@imsp009a.netvigator.com> <361E36B6.859DD7EA@peoplesoft.com>
Followup-To: comp.databases.sybase,comp.databases.ms-sqlserver,comp.databases.oracle.server
Organization: Netvigator
Newsgroups: comp.databases.sybase,comp.databases.ms-sqlserver,comp.databases.oracle.server


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

Eric Miner (eric_miner@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?
: 
: 
: