Dear Support!

Can the engine use index on "non-equal" filter expression?

F. e:
Create Table(ID integer not null primary key);

I write:

where
   (ID <> 5)

or

where
   not (ID = 5)

Would engine use the primary index somehow?

Cos I read in an older SQL book that the "NOT" / "<>" forces some RDBMS to ignore index, because they cannot force the ranged (from-to) logic in index search.

As I saw the engine substitutes "NOT" with "<>".

In boolean logic I might use
  "(somebooleanfield = False)"
for
 "not (somebooleanfield)" / "(somebooleanfield <> True)"

But in other (numeric, string) expressions I cannot do same substitution.

That's why I asked about index usage - how can I calculate with query's speed after index creation.

Thank you for any information about it!

Regards
   dd

Hi,

> Can the engine use index on "non-equal" filter expression?

Yes, but you can test that yourself by having a look at the execution
plan, e.g.:

Filtering
---------

The following filter condition was applied to the anforderungen table:

"gruppe" <> 'IM3'

Index scan (Anforderungen.Gruppe_INX): 25914 keys, 396KB estimated cost

================================================================================
26069 row(s) returned in 0 secs
================================================================================

Uli

Uli Becker wrote:

> Index scan

That being the important part - took me a while to understand that. If
it says "Row scan" then there is no index in use.

--

Matthew Jones