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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 17 Nov 2011 09:00:57 -0500
Message-ID: <053c01cca531$54f9f670$feede350$_at_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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Thursday, November 17, 2011 8:54 AM To: ChrisDavid.Taylor_at_ingrambarge.com; 'Stephens, Chris' Cc: oracle-l_at_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

Received on Thu Nov 17 2011 - 08:00:57 CST