What is the difference between comparison operators on Oracle?

Asked

Viewed 3,132 times

4

In Oracle there are several operators to make "different" comparisons, 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 I wonder if there is any difference in performance, version between them? If not, is expected to discontinue for any of them?

sql oracle pl-sql

  • That’s not just in Oracle, at least some of those operators also operate in the same way in MySQL, then I imagine it’s something of its own SQL.

  • I didn’t know, I’ve been living more in the oracle

5 answers

4

I only found information indicating that not all work on all platforms.

There is no documentation of differences at all, so treat them as identical. Even if they may eventually perform differently, and I see no reason for this, there is no guarantee that it will always be so. Nor would it make sense to be different. And they cannot change the behavior that is already documented.

In fact someone took the test and noticed that it gives the same result. There were those who disputed.

3


Giving a read on documentation, all these operators do the same thing. There is no difference between performance and or execution order.

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

inserir a descrição da imagem aqui

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

  • 1

    Missed the ¬= in the figure.

  • True, funny that the image was removed from the official documentation.

  • 1

    @Brunocosta Probably whoever created the image forgot this.

  • So I checked the other answers operator = is the oldest and least used.

2

According to this page of the oracle, 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 of < > should be <>. They’re not the greatest (>) and the smallest (<) which are described just below in the table.

That is, they are all equivalent.

SQL in Oracle is compiled to an internal tree shape. This means that there is no measurable performance difference between them.

2

These "not equal" operators must be equivalent, but there is a note from Scott Canaan 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 damaged by the size and format of the queries than the signals used, but of course it should be tested individually each query. If you want to test some on your project, I recommend:

SQL Performance Analyzer

Note by Scott Canaan

2

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

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.