Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: using index

Re: using index

From: Eric Miner <eric_miner_at_peoplesoft.com>
Date: 1998/10/09
Message-ID: <361E36B6.859DD7EA@peoplesoft.com>#1/1

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 Fri Oct 09 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US