RE: Strategies for dealing with (NOT EQUAL) conditions and indexes
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, 'Sidney Chen' <huanshengchen@xxxxxxxxx>, "'taral.desai@xxxxxxxxx'" <taral.desai@xxxxxxxxx>
- Date: Thu, 17 Nov 2011 08:29:22 -0600
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".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
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.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Taylor, Chris David
Sent: Thursday, November 17, 2011 7:58 AM
To: '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
Is there a difference in the way Oracle deals with "!=" versus "<>" inequality
/nonequality conditions in the optimizer?
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.
From: Sidney Chen [mailto:huanshengchen@xxxxxxxxx]
Sent: Thursday, November 17, 2011 6:53 AM
To: taral.desai@xxxxxxxxx
Cc: Taylor, Chris David; Stephens, Chris; Mark W. Farnham;
oracle-l@xxxxxxxxxxxxx
Subject: Re: Strategies for dealing with (NOT EQUAL) conditions and indexes
it's all about cost, the index is not used because the Optimizer think it's
cheaper(lower cost) to do a full table scan, not because it can't do a index
scan for an not equal predicate.
just as Mark said, if you make val1 popular enough, the index will be used. I
remove the primary key to make sure around 90% var1 = 12. this tick to show how
index scan can happen for not equal predicate.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
