RE: Performance Difference Between != and <>?

From: Scott Canaan <srcdco_at_rit.edu>
Date: Mon, 25 Sep 2006 08:32:10 -0400
Message-ID: <D0A642D37DE30842AA667A9AFFE3951A03674F5B@svits11.main.ad.rit.edu>

Jonathan,

Thank you for your answer. We did do an explain plan on both versions of the statement and they were identical, which is what is so puzzling about this. According to the documentation, the two forms of not equal are the same (along with ^= and one other that I can't type), so it makes no sense to me why there is any difference in performance.

Scott Canaan '88 (Scott.Canaan_at_rit.edu)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.

-----Original Message-----

From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Friday, September 22, 2006 4:34 PM To: Scott Canaan; oracle-l_at_freelists.org Subject: Re: Performance Difference Between != and <>?

Try running your query through explain plan (or autotrace) and see what that says -

SQL> select count(*) from t1 where id != 99;

| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)|
Time     |


------------------------------------------------------------------------

-------

|   0 | SELECT STATEMENT      |       |     1 |     5 |   599   (6)|
00:00:08 |
|   1 |  SORT AGGREGATE       |       |     1 |     5 |            |

|
|* 2 | INDEX FAST FULL SCAN| T1_PK | 999K| 4882K| 599 (6)| 00:00:08 |

Predicate Information (identified by operation id):

2 - filter("ID"<>99)

According to this, "!=" is considered to be the same as "<>". Similarly:

select count(*) from t1 where small_vc ='x' and exists (

select 1 from t1 where small_vc != 'Y' )

| 0 | SELECT STATEMENT | | 1 | 11 | 9633 (2)| 00:01:56 |

|   1 |  SORT AGGREGATE     |      |     1 |    11 |            |
|
|*  2 |   FILTER            |      |       |       |            |
|
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    11 |  4816   (2)|

00:00:58 |
|* 4 | TABLE ACCESS FULL| T1 | 999K| 10M| 4816 (2)| 00:00:58 |

Predicate Information (identified by operation id):

   2 - filter( EXISTS (SELECT 0 FROM "T1" "T1" WHERE "SMALL_VC"<>'Y'))
   3 - filter("SMALL_VC"='x')
   4 - filter("SMALL_VC"<>'Y')

This was 10.2.0.1 - I'd be very surprised if any other version of Oracle were significantly different.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Original Message ----- From: "Scott Canaan" <srcdco_at_rit.edu> To: <oracle-l_at_freelists.org> Sent: Friday, September 22, 2006 9:20 PM 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_at_rit.edu)

(585) 475-7886

"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.

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

Received on Mon Sep 25 2006 - 07:32:10 CDT