What is the difference between comparison operators in Oracle?

5

In Oracle there are several operators to make comparisons of "different", such as:

<>
¬=
!=
^=

Example:

Select * from tabel where nomeTabela <> 's';
Select * from tabel where nomeTabela != 's';
Select * from tabel where nomeTabela ^= 's';
Select * from tabel where nomeTabela ¬= 's';

All will return the same result, said would you like to know if there is any performance difference, version between them? If not, is there a discontinuity forecast for any of them?

    
asked by anonymous 30.10.2017 / 13:58

5 answers

2

By reading the documentation , all these operators do the same thing. There is no difference between perfomance and or order of execution.

The following image shows the order of execution, which appears to be the same for all condition operators:

If you search more on the subject, you will see that for SQL in general, independent of the DBMS, the use of <> as the standard in the comparison of inequalities is favored.

    
30.10.2017 / 14:09
2

According to this oracle page , in table 3-4 of this page:

  

!=
^=
< >
¬=

     

Inequality test. Some forms of the inequality operator may be unavailable on some platforms.

Translating:

  

Inequality test. Some forms of the inequality operator may not be available on some platforms.

I believe the < > case should be <> . They are not the largest-that (% with_%) and the least-that (% with_%) that are described just below in the table.

That is, they are all equivalent.

The SQL in Oracle is compiled into an inner form of the tree. This means there is no measurable performance difference between them.

    
30.10.2017 / 14:05
2

I just found information indicating that not all of them work on all platforms.

There is no documentation of differences in anything, so treat them as being identical. Even though they may eventually perform differently, and I see no reason for it, there is no guarantee that it will always be so. It would not make sense to be different. And they can not change behavior that is already documented.

In fact someone took the test and noticed that it gives the same result . Some people answered.

    
30.10.2017 / 14:05
2

These "not equal" operators should be equivalent, but there is a Scott Canaan note that suggests, in Oracle 10.2, they can produce different execution plans and therefore different execution speeds:

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 in a sub-second. If you use <> , it takes 7 seconds to return. Both return the right answer.

I believe that the speed would be more broken by the size and format of the queries than by the signs used, but of course each query should be tested individually. If you want to test some in your project, I recommend:

SQL Performance Analyzer

  

Scott Canaan Note

    
30.10.2017 / 14:08
2

There is no difference in performance just about the syntax that is different. But if you want to make a query compatible with the SQL standard, use the "< >" which is also accepted on other platforms. About the discontinuity of the operators, all are currently in the official Oracle documentation.

    
30.10.2017 / 14:10