RE: Performance Difference Between != and <>?

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: srcdco@xxxxxxx, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Sep 2006 21:12:07 +0800

Do you have any Stored Outline ? Stored Outlines do _exact_ (literal) matches
so if you have one Stored Outline for, say, the SQL with a "!=" and
none for the SQL with a "<>" (or a vice versa), the Stored Outline
might be using hints ? (although, come to think of it, your EXPLAIN PLAN
should have shown the hints if executing a Stored Outline ?)

At 08:32 PM Monday, Scott Canaan wrote:

   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.
----- Original Message -----
From: "Scott Canaan" <srcdco@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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.

Hemant K Chitale

-- //

Other related posts: