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

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Taylor, Chris David'" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, "'Sidney Chen'" <huanshengchen@xxxxxxxxx>, <taral.desai@xxxxxxxxx>
  • Date: Thu, 17 Nov 2011 10:15:35 -0500

The inequality does seem to force at best a fast full index scan (ffs). I'm
a bit surprised it does not routinely check the cost of sourcing that as the
relevant pair of index range scans greater than and less than.
Once you're into a fast full index scan at best and have to probe the table
as well, I have not found a cost differential where the optimizer will
choose the ffs from the naturally written query.

This:

select --+ gather_plan_statistics t1.a,t1.d from junk13 t1,
(select --+ no_merge       rowid from junk13 where a!= '1') t2 where
t1.rowid = t2.rowid and t1.d != 'not aA'

will force the issue (and you can cut and paste out the cost profile and
plan from the badly formatted earlier message or test the similar yourself).

Oddly, even

select --+ gather_plan_statistics a,d from junk13 where junk13.rowid in
   (select   /* + no_merge */  rowid from junk13 where a!= '1') and d!= 'not
aA'

still does the full table scan (fts), even though (in my actual case) the
fts is over 25000 buffers and the ffs is about 10,000 buffers and a single
index probe.

So either I'm missing something or this is indeed an area where the CBO
could be improved (and it seems like a common enough case that it would be
useful).

Of course the greater than or less than rewrite gets this pretty fast with
the concatenation, but it seems that a "not equals range scan" access method
would be a good optimizer team investment, since I have it in mind that
would be cheaper than concatenating the two result sets (and it would be
more convenient for us.)

Jonathan Lewis has a whole new course on "beating" the CBO. Again, there
might be a more natural way around this current problem than I'm seeing, but
there are many cases that the CBO team either has not gotten to yet or which
are judged too special case for them to implement. Jonathan's course, I
believe, delves into several? many? of these cases. I haven't seen the
course materials (yet), but I think they will reflect JL's passion and
intellect for knowing how to get the best possible plan when the CBO cannot
(currently) in a particular case. JL might want to comment on his course if
I have mischaracterized it (or just extend my remarks even if they are
correct).

Good luck,

mwf

-----Original Message-----
From: Taylor, Chris David [mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx] 
Sent: Thursday, November 17, 2011 9:29 AM
To: Taylor, Chris David; 'Sidney Chen'; 'taral.desai@xxxxxxxxx'
Cc: 'Stephens, Chris'; 'Mark W. Farnham'; 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Strategies for dealing with (NOT EQUAL) conditions and indexes

Well it looks like there is no noticeable difference between <> and != as
far as the optimizer is concerned.

Here's what started me down this road and led me to asking the original
question:

From DBMS_SQLTUNE.REPORT_TUNING_TASK  (see explicit Rationale section at the
bottom)


2- Restructure SQL finding (see plan 2 in explain plans section)
----------------------------------------------------------------
  Predicate "CHEMREG_SAMPLE"."CONTAINER_STATUS"<>'UNAVAIL' used at line ID
10
  of the execution plan is an inequality condition on indexed column
  "CONTAINER_STATUS". This inequality condition prevents the optimizer from
  selecting indices  on table "CHEMREG"."CHEMREG_SAMPLE".

<snip>


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


Other related posts: