Oracle<> ，！=，^ =运算符 [英] Oracle <> , != , ^= operators

问题描述

我想知道这些操作符的区别，主要是它们的性能差异。

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.

然后我在 dba-oracle.com ，
它表明在10.2以后的性能可能是完全不同的。

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.

推荐答案

我已经测试了不同的语法的性能在Oracle中是相等的运算符。我试图消除所有外部影响的测试。

I have tested the performance of the different syntax for the not equal operator in Oracle. I have tried to eliminate all outside influence to the test.

我使用的是一个11.2.0.3数据库。在开始测试之前，没有其他会话已连接并且数据库已重新启动。

I am using an 11.2.0.3 database. No other sessions are connected and the database was restarted before commencing the tests.

使用单个表和主键序列创建了一个模式

A schema was created with a single table and a sequence for the primary key

CREATE TABLE loadtest.load_test (
  id NUMBER NOT NULL,
  a VARCHAR2(1) NOT NULL,
  n NUMBER(2) NOT NULL,
  t TIMESTAMP NOT NULL
);

CREATE SEQUENCE loadtest.load_test_seq
START WITH 0
MINVALUE 0;

表被索引以提高查询的性能。

The table was indexed to improve the performance of the query.

ALTER TABLE loadtest.load_test
ADD CONSTRAINT pk_load_test
PRIMARY KEY (id)
USING INDEX;

CREATE INDEX loadtest.load_test_i1
ON loadtest.load_test (a, n);

使用序列 SYSDATE 通过DBMS_RANDOM（AZ）和（0-99）为其他两个字段的时间戳和随机数据。

Ten million rows were added to the table using the sequence, SYSDATE for the timestamp and random data via DBMS_RANDOM (A-Z) and (0-99) for the other two fields.

SELECT COUNT(*) FROM load_test;

COUNT(*)
----------
10000000

1 row selected.

已分析架构以提供良好的统计信息。

The schema was analysed to provide good statistics.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'LOADTEST', estimate_percent => NULL, cascade => TRUE);

这三个简单的查询是： -

The three simple queries are:-

SELECT a, COUNT(*) FROM load_test WHERE n <> 5 GROUP BY a ORDER BY a;

SELECT a, COUNT(*) FROM load_test WHERE n != 5 GROUP BY a ORDER BY a;

SELECT a, COUNT(*) FROM load_test WHERE n ^= 5 GROUP BY a ORDER BY a;

这些完全相同，除了不等于运算符的语法> and！= but also =）

These are exactly the same with the exception of the syntax for the not equals operator (not just <> and != but also ^= )

首先，每个查询运行时不收集结果，以消除缓存的影响。

First each query is run without collecting the result in order to eliminate the effect of caching.

下一次计时和自动跟踪已打开，以收集查询和执行计划的实际运行时间。

Next timing and autotrace were switched on to gather both the actual run time of the query and the execution plan.

SET TIMING ON

SET AUTOTRACE TRACE

现在查询运行。首先是<>

Now the queries are run in turn. First up is <>

> SELECT a, COUNT(*) FROM load_test WHERE n <> 5 GROUP BY a ORDER BY a;

26 rows selected.

Elapsed: 00:00:02.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2978325580

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |    26 |   130 |  6626   (9)| 00:01:20 |
|   1 |  SORT GROUP BY        |              |    26 |   130 |  6626   (9)| 00:01:20 |
|*  2 |   INDEX FAST FULL SCAN| LOAD_TEST_I1 |  9898K|    47M|  6132   (2)| 00:01:14 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N"<>5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22376  consistent gets
      22353  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        459  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed

下一步！=

> SELECT a, COUNT(*) FROM load_test WHERE n != 5 GROUP BY a ORDER BY a;

26 rows selected.

Elapsed: 00:00:02.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2978325580

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |    26 |   130 |  6626   (9)| 00:01:20 |
|   1 |  SORT GROUP BY        |              |    26 |   130 |  6626   (9)| 00:01:20 |
|*  2 |   INDEX FAST FULL SCAN| LOAD_TEST_I1 |  9898K|    47M|  6132   (2)| 00:01:14 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N"<>5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22376  consistent gets
      22353  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        459  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed

最后^ =

> SELECT a, COUNT(*) FROM load_test WHERE n ^= 5 GROUP BY a ORDER BY a;

26 rows selected.

Elapsed: 00:00:02.10

Execution Plan
----------------------------------------------------------
Plan hash value: 2978325580

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |    26 |   130 |  6626   (9)| 00:01:20 |
|   1 |  SORT GROUP BY        |              |    26 |   130 |  6626   (9)| 00:01:20 |
|*  2 |   INDEX FAST FULL SCAN| LOAD_TEST_I1 |  9898K|    47M|  6132   (2)| 00:01:14 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N"<>5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22376  consistent gets
      22353  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        459  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed

这三个查询的执行计划是相同的， ，2.13和2.10秒。

The execution plan for the three queries is identical and the timings 2.12, 2.13 and 2.10 seconds.

应该注意的是，无论查询中使用哪种语法，执行计划总是显示<>

It should be noted that whichever syntax is used in the query the execution plan always displays <>

每个运算符语法重复十次测试。这些是时间： -

The tests were repeated ten times for each operator syntax. These are the timings:-

<>

2.09
2.13
2.12
2.10
2.07
2.09
2.10
2.13
2.13
2.10

!=

2.09
2.10
2.12
2.10
2.15
2.10
2.12
2.10
2.10
2.12

^=

2.09
2.16
2.10
2.09
2.07
2.16
2.12
2.12
2.09
2.07

虽然有一些差异的百分之二秒，它是不重要的。每个语法选择的结果是相同的。

Whilst there is some variance of a few hundredths of the second it is not significant. The results for each of the three syntax choices are the same.

语法选项被解析，优化并在同一时间以相同的努力返回。因此，在这次测试中，使用一个在另一个之上没有明显的好处。

The syntax choices are parsed, optimised and are returned with the same effort in the same time. There is therefore no perceivable benefit from using one over another in this test.

啊BC，你说：在我的测试中，我相信有一个真正的区别，你不能证明它不是。

"Ah BC", you say, "in my tests I believe there is a real difference and you can not prove it otherwise".

是的，我说，这是完全正确的。您尚未显示测试，查询，数据或结果。所以我没有什么可说的你的结果。我已经表明，在所有其他条件相同的情况下，使用哪种语法无关紧要。

Yes, I say, that is perfectly true. You have not shown your tests, query, data or results. So I have nothing to say about your results. I have shown that, with all other things being equal, it doesn't matter which syntax you use.

为什么我看到一个在我的测试中更好？

"So why do I see that one is better in my tests?"

好问题。有几种可能性： -

Good question. There a several possibilities:-

1. 您的测试有缺陷（您没有消除外部因素 -
   其他工作负载，没有给出关于
   的信息，我们可以做出明智的决定）


2. 您的查询是一个特殊情况（显示查询，我们可以讨论它）。


3. 您的数据是一种特殊情况（也许 - 但是如何 - 我们看不到）。


4. 还有一些外部影响力。

1. Your testing is flawed (you did not eliminate outside factors - other workload, caching etc You have given no information about which we can make an informed decision)
2. Your query is a special case (show me the query and we can discuss it).
3. Your data is a special case (Perhaps - but how - we don't see that either).
4. There is some other outside influence.

使用一种语法对另一种语法没有好处。我相信<>！=和^ =是同义词。

I have shown via a documented and repeatable process that there is no benefit to using one syntax over another. I believe that <> != and ^= are synonymous.

如果你认为其他情况还不错，

If you believe otherwise fine, so

a）显示一个我可以尝试自己的文档示例

a) show a documented example that I can try myself

和

认为是最好的。如果我是正确的，没有区别，没关系。

b) use the syntax which you think is best. If I am correct and there is no difference it won't matter. If you are correct then cool, you have an improvement for very little work.

但是Burleson说的更好，我相信他比你更重要，Faroult，Lewis， Kyte和所有其他的屁股。

"But Burleson said it was better and I trust him more than you, Faroult, Lewis, Kyte and all those other bums."

他说这更好吗？我不这么认为。他没有提供任何明确的例子，测试或结果，只是与某人说，！=更好，然后引用了他们的一些帖子。

Did he say it was better? I don't think so. He didn't provide any definitive example, test or result but only linked to someone saying that != was better and then quoted some of their post.

显示不告诉。

这篇关于Oracle&lt;&gt; ，！=，^ =运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！