RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Thu, 17 Nov 2011 11:35:57 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6A33D07816_at_SPOBMEXC14.adprod.directory>

Interesting. Between that entry and Mark F's information earlier, I now understand more of the "why" an index range scan is not available when using an inequality.

Very interesting stuff indeed.

I do like Mark's and Richard's approach of using x < y OR x > y to get a RANGE scan. I'm going to have keep that in my toolbox.

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.

-----Original Message-----
From: Dom Brooks [mailto:dombrooks_at_hotmail.com] Sent: Thursday, November 17, 2011 11:21 AM To: mwf_at_rsiz.com
Cc: Taylor, Chris David; Sidney Chen; <taral.desai_at_gmail.com>; Stephens, Chris; <oracle-l_at_freelists.org>; Jonathan Lewis Subject: Re: Strategies for dealing with (NOT EQUAL) conditions and indexes

For more info on this behaviour see:
http://richardfoote.wordpress.com/2008/08/13/indexes-and-not-equal-not-now-john/

One alternative if absolutely necessary might be an FBI using CASE for example to turn inequality predicate into equality.

Cheers,
Dominic

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 17 2011 - 11:35:57 CST