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:
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. ----- 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 http://web.singnet.com.sg/~hkchital
-- //www.freelists.org/webpage/oracle-l
