Oracle <> , != , ^= operators

Question

I want to know the difference of those operators, mainly their performance difference.

I have had a look at Difference between <> and != in SQL, it has no performance related information.

Then I found this on dba-oracle.com, it suggests that in 10.2 onwards the performance can be quite different.

I wonder why? does != always perform better then <>?

NOTE: Our tests, and performance on the live system shows, changing from <> to != has a big impact on the time the queries return in. I am here to ask WHY this is happening, not whether they are same or not. I know semantically they are, but in reality they are different.

Solution 1

You reference the article on the Burleson site. Did you follow the link to the Oracle-L archive? And did you read the other emails replying to the email Burleson cites?

I don't think you did, otherwise you wouldn't have asked this question. Because there is no fundamental difference between != and <>. The original observation was almost certainly a fluke brought about by ambient conditions in the database. Read the responses from Jonathan Lewis and Stephane Faroult to understand more.

---

" Respect is not something a programmer need to have, its the basic attitude any human being should have"

Up to a point. When we meet a stranger in the street then of course we should be courteous and treat them with respect.

But if that stranger wants me to design my database application in a specific way to "improve performance" then they should have a convincing explanation and some bulletproof test cases to back it up. An isolated anecdote from some random individual is not enough.

Solution 2

The writer of the article, although a book author and the purveyor of some useful information, does not have a good reputation for accuracy. In this case the article was merely a mention of one persons observations on a well known Oracle mailing list. If you read through the responses you will see the assumptions of the post challenged, but no presumption of accuracy. Here are some excerpts:

Try running your query through explain plan (or autotrace) and see what that says... According to this, "!=" is considered to be the same as "<>"... Jonathan Lewis

Jonathan Lewis is a well respected expert in the Oracle community.

Just out of curiosity... Does the query optimizer generate a different execution plan for the two queries? Regards, Chris

.

Might it be bind variable peeking in action? The certain effect of writing != instead of <> is to force a re-parse. If at the first execution the values for :id were different and if you have an histogram on claws_doc_id it could be a reason. And if you tell me that claws_doc_id is the primary key, then I'll ask you what is the purpose of counting, in particular when the query in the EXISTS clause is uncorrelated with the outer query and will return the same result whatever :id is. Looks like a polling query. The code surrounding it must be interesting.

Stéphane Faroult

.

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.

.

Do the explain plans look the same? Same costs?

The following response is from the original poster.

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

.

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)

.

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 ?)

.

Have you tried going beyond just explain & autotrace and running a full 10046 level 12 trace to see where the slower version is spending its time? This might shed some light on the subject, plus - be sure to verify that the explain plans are exactly the same in the 10046 trace file (not the ones generated with the EXPLAIN= option), and in v$sqlplan. There are some "features" of autotrace and explain that can cause it to not give you an accurate explain plan.

Regards, Brandon

.

Is the phenomenon totally reproducible ?

Did you check the filter_predicates and access_predicates of the plan, or just the structure. I don't expect any difference, but a change in predicate order can result in a significant change in CPU usage if you are unlucky.

If there is no difference there, then enable rowsource statistics (alter session set "_rowsource_execution_statistics"=true) and run the queries, then grab the execution plan from V$sql_plan and join to v$sql_plan_statistics to see if any of the figures about last_starts, last_XXX_buffer_gets, last_disk_reads, last_elapsed_time give you a clue about where the time went.

If you are on 10gR2 there is a /*+ gather_plan_statistics */ hint you can use instead of the "alter session".

Regards Jonathan Lewis

At this point the thread dies and we see no further posts from the original poster, which leads me to believe that either the OP discovered an assumption they had made that was not true or did no further investigation.

I will also point out that if you do an explain plan or autotrace, you will see that the comparison is always displayed as <>.

Here is some test code. Increase the number of loop iterations if you like. You may see one side or the other get a higher number depending on the other activity on the server activity, but in no way will you see one operator come out consistently better than the other.

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 AS (SELECT level c1 FROM dual CONNECT BY level <=144000);
CREATE TABLE t2 AS (SELECT level c1 FROM dual CONNECT BY level <=144000);

SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
   vStart  Date;
   vTotalA Number(10) := 0;
   vTotalB Number(10) := 0;
   vResult Number(10);
BEGIN   
   For vLoop In 1..10 Loop
      vStart := sysdate;
      For vLoop2 In 1..2000 Loop
         SELECT count(*) INTO vResult FROM t1 WHERE t1.c1 = 777 AND EXISTS
            (SELECT 1 FROM t2 WHERE t2.c1 <> 0);
      End Loop;
      vTotalA := vTotalA + ((sysdate - vStart)*24*60*60);

      vStart := sysdate;
      For vLoop2 In 1..2000 Loop
         SELECT count(*) INTO vResult FROM t1 WHERE t1.c1 = 777 AND EXISTS
            (SELECT 1 FROM t2 WHERE t2.c1 != 0);
      End Loop;
      vTotalB := vTotalB + ((sysdate - vStart)*24*60*60);

      DBMS_Output.Put_Line('Total <>: ' || RPAD(vTotalA,8) || '!=: ' || vTotalB);
      vTotalA := 0;
      vTotalB := 0;
   End Loop;

END;

Solution 3

A Programmer will use !=

A DBA will use <>

If there is a different execution plan it may be that there are differences in the query cache or statistics for each notation. But I don't really think it is so.

Edit:

What I mean above. In complex databases there can be some strange side effects. I don't know oracle good enough, but I think there is an Query Compilation Cache like in SQL Server 2008 R2. If a query is compiled as new query, the database optimiser calculates a new execution plan depending on the current statistics. If the statistics has changed it will result in a other, may be a worse plan.

Comments

[edze]

May be ;) I have seen a lot of code an if a programmer makes a query it's at the most !=

[aF.]

That's probably cause programmers are used to that from other languages (.net C#, vb, java, c, c++, etc.)

[Ollie]

I'm both an OCP developer and OCP DBA, which should I be using? :-) FWIW, I tend to use != rather than <>

[edze]

Sometimes I catch myself doing some mixing :)

[a_horse_with_no_name]

Actually the (ANSI) SQL standard only defines <>. != is an extension by some (if not all) DBMS.

[Bob Jarvis - Слава Україні]

Personally I always use <> when writing SQL. I use != when writing C or one of its derivatives. I use ~= when writing Smalltalk. I use ^= (should actually be a funky IBM-esque "straight-bar-with-vertical-descender' character instead of a caret, but what can you do?) when writing PL/I (which I haven't touched for nearly 25 years). There's a TON of different ways to say "NOT EQUAL" - which I seem to recall is what you use in COBOL. :-)

[Bob Jarvis - Слава Україні]

@aF - hey, me too. Where's my raise?!?!?

[Junchen Liu]

@BobJarvis != faster then <> has been tested proven, the question is not ask "if the have any difference", the question is why

[Walter Mitty]

I used to be a programmer some 30 years ago. When I learned SQL, "<>" was available, but "!=" was not. So I got into the habit of using "DBA style" in this case. That doesn't make me a DBA or a programmer. Come on, kids.

[APC]

" != faster then <> has been tested proven". Horse feathers! Where are the benchmarks?

[Ollie]

I can't find any reliable (i.e. defined, repeatable process) examples of one being faster than the other. When I tested this against a table with nearly 65 million records the difference was tiny and not enough to postulate about their relative efficiency. Perhaps if the table had 500 million records... but I'm not that interested.

[Bob Jarvis - Слава Україні]

FWIW - I'll take Jonathan Lewis's word on anything Oracle-wise.

[Bob Jarvis - Слава Україні]

Not constructive. Voting to close.

[Junchen Liu]

where is the "The writer of the article, although a book author and the purveyor of some useful information, does not have a good reputation for accuracy" came from? you have to give some evidence please. without prove I am afraid I have to call you a liar.

[Leigh Riffel]

Have you compared explain plans, autotrace results, 10046 level 12 trace output? Have you filed an SR with Oracle? Can you produce a test case similar to mine, but with different results showing a benefit to one not equals form over the other?

[Leigh Riffel]

You are welcome to not beleive me without proof. Here are some things that lead me to my conclusion: asktom.oracle.com/pls/asktom/… asktom.oracle.com/pls/apex/… oraclesponge.wordpress.com/2005/04/11/banned-by-burleson jonathanlewis.wordpress.com/2006/12/28/missing-the-point jonathanlewis.wordpress.com/2007/01/02/superfluous-updates jonathanlewis.wordpress.com/2007/11/10/analysing-statspack8 jonathanlewis.wordpress.com/2008/06/08/scientific-method

[Leigh Riffel]

jlcomp.demon.co.uk/untested.html kevinclosson.wordpress.com/2007/07/17/… freelists.org/post/oracle-l/Recent-Email-from-Don-Burleson,1 jonathanlewis.wordpress.com/2010/08/26/dmca jonathanlewis.wordpress.com/2008/06/10/firefox oaktable.net/content/burleson-buys-bmc nlitchfield.blogspot.com/2005_04_01_archive.html

[Junchen Liu]

its a dog fight between Jonathan and Burleson, how sad Jonathan used his wordpress to attack other people. money? customer? jealous, competition? I don't care. but it seems all your conclusions come from Jonathan's mouth, its personal, and you just can't take one man's word against another man

[Leigh Riffel]

Huh? Check the links again. I cite five other individuals not including the numerous corroborations in the comments.

[Jon Heller]

@BobJarvis Perhaps you can answer my (somewhat) related question about inequality operators: stackoverflow.com/q/9305925/409172

[Admin]

@Bob - In a sense, I'm not sure he would want you to. The reason why the Jonathan Lewis's of this world are so useful is because the stuff they say is usually testable and reproducible. You should probably check what they say rather than always "take their word for it" - not least because that is a great way of cementing it in your memory ;-)

[AnBisw]

+1 Not because for the answer's accuracy, but because it could be a nice Database joke you can crack while team building exercises :)

[ShoeLace]

sometimes i wish the internet had a delete button for times like this.