Oracle "(+)" Operator

Oracle (+) Operator

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Oracle , != , ^= operators

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.

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

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;

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

Next !=

> 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

Lastly ^=

> 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

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.

"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:-

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)
Your query is a special case (show me the query and we can discuss it).
Your data is a special case (Perhaps - but how - we don't see that either).
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) show a documented example that I can try myself

and

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.

"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.

Show don't tell.

Oracle (+) Operator with constant value

It's similar to something like this:

select *
from t1
  left outer join p2 on t1.psn_id  = p2.psn_id and p2.pat_att_id = 48;

So essentially a "filtered outer join" where only a subset of the rows of the table are joined.

If the (+) operator was removed from p2.pat_att_id(+) = 48 the query would become the equivalent of:

select *
from t1
  left outer join p2 on t1.psn_id = p2.psn_id 
where p2.pat_att_id = 48;

Which is something different.

This is another good example why the (+) operator should not be used any more (which even Oracle recommends).

Bound Oracle Text near operator to the same sentences

The query should look like this:

select score(1) 
from   tbl
where  contains(Paragraph, 'Near (coronavirus, death),20,false) 
                            WITHIN SENTENCE',1) > 0
;

That is - use the WITHIN operator.

Note that you must tell the index to recognize sentences first. That is: if you created the index with a statement like this:

create index ctxidx on tbl(Paragraph)
indextype is ctxsys.context
-- parameters(' ... ')
;

where the parameters (if you used that clause) don't say anything about "sentences", you will get an error if you try the query above - something along the lines of

DRG-10837: section sentence does not exist

First you will have to define "special" sections for sentences:

begin 
  ctx_ddl.create_section_group('my_section_group', 'AUTO_SECTION_GROUP');
  ctx_ddl.add_special_section('my_section_group', 'SENTENCE');
end;
/

With this in hand:

drop index ctxidx;

create index ctxidx on tbl(Paragraph)
indextype is ctxsys.context
parameters ('section group my_section_group')
;

Now you are ready to successfully run the query at the top of this Answer.

How to use q'[]' operator with variable in function

I have solved the issue. In the control file I removed OPTIONALLY ENCLOSED BY "'" like

FIELDS TERMINATED BY "," TRAILING NULLCOLS

and added it to each column except the column which had single quotes in it like

 column1 OPTIONALLY ENCLOSED BY "'",column2 OPTIONALLY ENCLOSED BY "'",
 columnwithquotes char "substr(:columnwithquotes , 2, length(:columnwithquotes ) - 2)"

Is there any difference between != and in Oracle Sql?

No there is no difference at all in functionality.

(The same is true for all other DBMS - most of them support both styles):

Here is the current SQL reference: https://docs.oracle.com/database/121/SQLRF/conditions002.htm#CJAGAABC

The SQL standard only defines a single operator for "not equals" and that is <>

Oracle SQL ternary operator or function?

You can use a CASE expression with LIKE:

SELECT *
FROM   my_table
WHERE  CASE WHEN my_col LIKE '%something%' THEN 'Y' ELSE 'N' END = 'Y';

or DECODE and INSTR:

SELECT *
FROM   my_table
WHERE  DECODE( INSTR( my_col, 'something' ), 0, 'N', 'Y' ) = 'Y';

or just simply use LIKE:

SELECT *
FROM   my_table
WHERE  my_col LIKE '%something%';

INSTR:

SELECT *
FROM   my_table
WHERE  INSTR( my_col, 'something' ) > 0;

or REGEXP_LIKE:

SELECT *
FROM   my_table
WHERE  REGEXP_LIKE( my_col, 'something' );

db<>fiddle here