Strategies for dealing with (NOT EQUAL) conditions and indexes

From: Taylor, Chris David <>
Date: Tue, 15 Nov 2011 13:10:08 -0600
Message-ID: <>

I often puzzle over the best way to deal with coding for NOT EQUAL conditions ( where x != y) in SQL code when I'm looking at performance tuning. In some cases, it's just a matter of education and getting a developer to look at what values are actually being stored in the table and have them look for the values they actually want versus the one they don't want.

But sometimes, these values are changing and the developer knows he/she wants ALL BUT THIS ONE value but he/she cannot know what possible values *might* exist.

Obviously the use of != causes indexes to be not available (except for a trick or two) to the optimizer, so I'm curious what are some strategies to turn a != into an = condition when the possible values aren't known or are too many to code for?

Could you use an EXISTS or IN statement and a subquery, or is there an alternative I haven't considered?

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Received on Tue Nov 15 2011 - 13:10:08 CST

Original text of this message