RE: Performance Difference Between != and <>?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <mwf@xxxxxxxx>, <srcdco@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Sep 2006 07:39:27 -0500
Not an all inclusive little test but it appears at least in 10.1.0.2 it
gets pared into a "<>" for either (notice the filter line for each
plan):
Plan for: select ename from emp where ename != 'KING'
------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 13 | 78 | 3 (0)| 00:00:01
|
|* 1 | TABLE ACCESS FULL| EMP | 13 | 78 | 3 (0)| 00:00:01
|
------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"<>'KING')
Plan for: select ename from emp where ename <> 'KING'
------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 13 | 78 | 3 (0)| 00:00:01
|
|* 1 | TABLE ACCESS FULL| EMP | 13 | 78 | 3 (0)| 00:00:01
|
------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"<>'KING')
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium March 4-8, 2007. Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
Sent: Sunday, September 24, 2006 12:45 PM
To: srcdco@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Performance Difference Between != and <>?
I'm pretty sure the lexical parse converts either != to <> or <> to !=,
but I'm not sure whether that affects whether the sql text will match a
stored outline.
The original text is preserved in the sql text area, so maybe that is
it. Otherwise I am mystified.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Scott Canaan
Sent: Friday, September 22, 2006 4:20 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Performance Difference Between != and <>?
Does anyone know why there's a big performance difference when using
!= vs. <>? I was just looking at a query that was written both ways and
there is a big difference in how long it takes to return data. The
query is:
Select count(*) from claws_doc_table where claws_doc_id = :id and exists
(select 1 from claws_person_id where status != 0);
If you use !=, it returns sub-second. If you use <>, it takes 7 seconds
to return. Both return the right answer. I've looked in the Oracle
documentation and can't find anything that would explain this. The
documentation says that they are interchangeable.
We are running Oracle 10.2.0.2 on Solaris 10, 64-bit.
Thank you,
Scott Canaan '88 (Scott.Canaan@xxxxxxx)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put
into it." - Tom Lehrer.
Other related posts:
