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

Really?

db_admin_at_REDDB> create table t1(col1 number not null);

Table created.

db_admin_at_REDDB> create index t1_idx1 on t1(col1);

Index created.

db_admin_at_REDDB> insert into t1(col1) values(1);

1 row created.

db_admin_at_REDDB> insert into t1(col1) values(2);

1 row created.

db_admin_at_REDDB> select /*+ gather_plan_statistics */ col1 from t1 where col1 != 1;

      COL1

         2

db_admin_at_REDDB> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 | |* 1 | INDEX FULL SCAN | T1_IDX1 | 1 | 1 | 1 |00:00:00.01 | 1 |

-----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 1: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





CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.



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