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

1. 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.
2. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Taylor, Chris David
Sent: Tuesday, November 15, 2011 2:10 PM To: 'oracle-l_at_freelists.org'
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.

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 16 2011 - 10:59:50 CST