Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>I am using an 11.2.0.3 database. No other sessions are connected and the database was restarted before commencing the tests.</p> <p>A schema was created with a single table and a sequence for the primary key</p> <pre><code>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; </code></pre> <p>The table was indexed to improve the performance of the query.</p> <pre><code>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); </code></pre> <p>Ten million rows were added to the table using the sequence, <code>SYSDATE</code> for the timestamp and random data via DBMS_RANDOM (A-Z) and (0-99) for the other two fields.</p> <pre><code>SELECT COUNT(*) FROM load_test; COUNT(*) ---------- 10000000 1 row selected. </code></pre> <p>The schema was analysed to provide good statistics.</p> <pre><code>EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =&gt; 'LOADTEST', estimate_percent =&gt; NULL, cascade =&gt; TRUE); </code></pre> <p>The three simple queries are:-</p> <pre><code>SELECT a, COUNT(*) FROM load_test WHERE n &lt;&gt; 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; </code></pre> <p>These are exactly the same with the exception of the syntax for the not equals operator (not just &lt;> and != but also ^= )</p> <p>First each query is run without collecting the result in order to eliminate the effect of caching.</p> <p>Next timing and autotrace were switched on to gather both the actual run time of the query and the execution plan.</p> <pre><code>SET TIMING ON SET AUTOTRACE TRACE </code></pre> <p>Now the queries are run in turn. First up is &lt;></p> <pre><code>&gt; SELECT a, COUNT(*) FROM load_test WHERE n &lt;&gt; 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"&lt;&gt;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 </code></pre> <p>Next !=</p> <pre><code>&gt; 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"&lt;&gt;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 </code></pre> <p>Lastly ^=</p> <pre><code>&gt; 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"&lt;&gt;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 </code></pre> <p>The execution plan for the three queries is identical and the timings 2.12, 2.13 and 2.10 seconds.</p> <p>It should be noted that whichever syntax is used in the query the execution plan always displays &lt;></p> <p>The tests were repeated ten times for each operator syntax. These are the timings:-</p> <pre><code>&lt;&gt; 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 </code></pre> <p>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.</p> <p>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.</p> <p>"Ah BC", you say, "in my tests I believe there is a real difference and you can not prove it otherwise".</p> <p>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.</p> <p>"So why do I see that one is better in my tests?"</p> <p>Good question. There a several possibilities:-</p> <ol> <li>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)</li> <li>Your query is a special case (show me the query and we can discuss it).</li> <li>Your data is a special case (Perhaps - but how - we don't see that either). </li> <li>There is some other outside influence.</li> </ol> <p>I have shown via a documented and repeatable process that there is no benefit to using one syntax over another. I believe that &lt;> != and ^= are synonymous.</p> <p>If you believe otherwise fine, so</p> <p>a) show a documented example that I can try myself</p> <p>and</p> <p>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.</p> <p>"But Burleson said it was better and I trust him more than you, Faroult, Lewis, Kyte and all those other bums."</p> <p>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.</p> <p>Show don't tell. </p>
    singulars
    1. This table or related slice is empty.
    1. POOracle <> , != , ^= operators
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USLunc
    1. USLunc
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOracle <> , != , ^= operators
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COBurleson implies that: *"These "not equal" operators are **supposed to be equivalent**, but ..."*
      singulars
      1. PO
      1. USypercubeᵀᴹ
    2. CO+1 for the test case. (But no bounty, since that's meant to be a reward for anyone who can prove the opposite. Although I think you will, by default, win half the bounty anyway in a few hours.)
      singulars
      1. PO
      1. USJon Heller
    3. CO@ypercube. ...but here is a guy saying otherwise. Burleson should have tested it and must take some responsibility for that, since he is a well linked source.
      singulars
      1. PO
      1. USLunc
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload