From: "Mark W. Farnham" <mwf@rsiz.com>
To: <mwf@rsiz.com>, <ChrisDavid.Taylor@ingrambarge.com>,
        "'Stephens, Chris'" <Chris.Stephens@adm.com>
Cc: <oracle-l@freelists.org>
References: <C5533BD628A9524496D63801704AE56D6A334C3EB5@SPOBMEXC14.adprod.directory> <D95BD5AFADBB0F4E9BB6C53F14D3A050057BA5A36D@JRCEXC1V1.research.na.admworld.com> <C5533BD628A9524496D63801704AE56D6A33D074D4@SPOBMEXC14.adprod.directory> <04b001cca530$67a7b880$36f72980$@rsiz.com>
In-Reply-To: <04b001cca530$67a7b880$36f72980$@rsiz.com>
Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
Date: Thu, 17 Nov 2011 09:00:57 -0500
Message-ID: <053c01cca531$54f9f670$feede350$@rsiz.com>
MIME-Version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
Content-Language: en-us
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: mwf@rsiz.com

Argh. I sent that carefully formatted to not wrap and be proportional, but
apparently the mail chain stripped all that off and wrapped the heck out of
it.

This:

select --+ gather_plan_statistics a,d from junk13 where (a < '1' or a >'1')
and d != 'not aA'

was the key point.

-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]
On Behalf Of Mark W. Farnham
Sent: Thursday, November 17, 2011 8:54 AM
To: ChrisDavid.Taylor@ingrambarge.com; 'Stephens, Chris'
Cc: oracle-l@freelists.org
Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

Okay, I did not realize the not equals was on the indexed column rather than
the non-indexed column.
 

In the following, there is an index on columns (a,b,c), and none on d. But a
is highly selective (one row in fact, since I didn't want to fool around and
find the break point).

 
The value on D is then just filtered out from the one row the index returns.
So if the costs are right, you can turn a single not equals into the
concatenation of a pair of index range scans with a table probe

instead of a fast full index scan or a full table scan.
<snip>


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