RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Nov 2011 11:59:50 -0500
a) what Chris Stephens already demo'd, that you still can get a fast full
index scan quite simply with the != for a not null column.
b) if the column is nullable, then you can still use the index easily as
long as you also don't want NULLs for that column indexed
select * from tab where x < y and x is not null
union all
select * from tab where x > y and x is not null
When y is a sufficiently popular value the pair of range scans will be a
cheaper row source than the full index scan (but in complex plans you may
have to manage this to not screw up the plan generated). Tucking this union
all in the from clause is often an easy way to do this. This also works of
course if the column is not nullable (as in Chris' example) and you don't
then need the "and is not null" clauses. (NOT NULL predicates allow index
usage in all versions you're likely able to get your hands on, though some
texts perpetuate the myth that any NULL reference in the predicate prevents
using the index. Unless you are using an index type that includes null
values IS NULL won't work of course since the null column value rowid
references won't be there. The CBO will use a multicolumn index for when at
least one column in the index is not nullable, if in fact it gets a lower
cost estimate. Standard indexes with all nullable columns of course can't be
guaranteed to return all IS NULL values (and a single column standard index
never will) so the CBO will do the right thing and not consider the indexes
in those cases. I suppose a dictionary stat could be collected that lets the
CBO know there are in fact no NULL values in a particular column, but I
don't believe that is either implemented or considered a useful optimization
to pursue.
When y, or some set of multiple members y are very popular values, you can
also shrink the index on x tremendously (or rather an index on xv, sorry for
the forward reference) by creating a virtual column where the popular values
are mapped to NULL and then putting the predicate on xv instead of x. When
there is one very popular value in a table (often a last status value, but
not always) you can of course convert that value to NULL and include and IS
NOT NULL in your predicate. Then the fast full scan when you don't want that
value less costly by the number of values no longer needed to be stored.
The implementation details of various flavors of this dodge are too long for
an oracle-l post. Unless the value you are avoiding is quite popular the
fast full scan that Chris Stephens demo'd will probably be cheapest and it
certainly is the simplest. Still, I come across cases of a small number of
extremely popular values quite often in transaction systems.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Taylor, Chris David
Sent: Tuesday, November 15, 2011 2:10 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: Strategies for dealing with (NOT EQUAL) conditions and indexes
I often puzzle over the best way to deal with coding for NOT EQUAL
conditions ( where x != y) in SQL code when I'm looking at performance
tuning.
In some cases, it's just a matter of education and getting a developer to
look at what values are actually being stored in the table and have them
look for the values they actually want versus the one they don't want.
But sometimes, these values are changing and the developer knows he/she
wants ALL BUT THIS ONE value but he/she cannot know what possible values
*might* exist.
Obviously the use of != causes indexes to be not available (except for a
trick or two) to the optimizer, so I'm curious what are some strategies to
turn a != into an = condition when the possible values aren't known or are
too many to code for?
Could you use an EXISTS or IN statement and a subquery, or is there an
alternative I haven't considered?
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
