RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
Date: Thu, 17 Nov 2011 10:15:35 -0500
Message-ID: <057b01cca53b$c2b9c5f0$482d51d0$_at_rsiz.com>
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_at_ingrambarge.com]
Sent: Thursday, November 17, 2011 9:29 AM
To: Taylor, Chris David; 'Sidney Chen'; 'taral.desai_at_gmail.com'
Cc: 'Stephens, Chris'; 'Mark W. Farnham'; 'oracle-l_at_freelists.org'
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>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 17 2011 - 09:15:35 CST