Difference Between "||" Operator and Concat Function in Oracle

What is the difference between || operator and concat function in Oracle?

There is no functional difference.

|| is the ANSI standard string concatenation operator (though, unfortunately, not every database <cough>SQL Server</cough> chooses to support the standard). Many databases support a CONCAT function so it may be easier to port code using CONCAT to different databases.

What is the string concatenation operator in Oracle?

It is ||, for example:

select 'Mr ' || ename from emp;

The only "interesting" feature I can think of is that 'x' || null returns 'x', not null as you might perhaps expect.

What is the difference between the UNION and CONCATENATION operators in terms of performance?

First up, UNION and CONCATENATION are subtly different.

CONCATENATION is equivalent to UNION-ALL. This combines the input tables and returns all the rows.

UNION combines the input tables. Then returns the distinct rows.

So UNION has an extra sort/distinct operation compared to CONCATENATION. How big this effect is depends on your data set.

You'll see CONCATENATION when the optimizer does an OR expansion. But note that from Oracle Database 12.2, this has changed:

• CONCATENATION is replaced with UNION-ALL.
• Each UNION-ALL branch can be subject to further query transformations, if applicable. This is not possible with
  CONCATENATION.
• Parallel queries can execute UNION-ALL branches concurrently. Again, this is not possible with CONCATENATION.

So UNION-ALL can come up with better plans for each operation below it. And run these at the same (if using parallel). So in many cases this will be faster than CONCATENATION.

String concatenation: concat() vs + operator

No, not quite.

Firstly, there's a slight difference in semantics. If a is null, then a.concat(b) throws a NullPointerException but a+=b will treat the original value of a as if it were null. Furthermore, the concat() method only accepts String values while the + operator will silently convert the argument to a String (using the toString() method for objects). So the concat() method is more strict in what it accepts.

To look under the hood, write a simple class with a += b;

public class Concat {
    String cat(String a, String b) {
        a += b;
        return a;
    }
}

Now disassemble with javap -c (included in the Sun JDK). You should see a listing including:

java.lang.String cat(java.lang.String, java.lang.String);
  Code:
   0:   new     #2; //class java/lang/StringBuilder
   3:   dup
   4:   invokespecial   #3; //Method java/lang/StringBuilder."<init>":()V
   7:   aload_1
   8:   invokevirtual   #4; //Method java/lang/StringBuilder.append:(Ljava/lang/String;)Ljava/lang/StringBuilder;
   11:  aload_2
   12:  invokevirtual   #4; //Method java/lang/StringBuilder.append:(Ljava/lang/String;)Ljava/lang/StringBuilder;
   15:  invokevirtual   #5; //Method java/lang/StringBuilder.toString:()Ljava/lang/    String;
   18:  astore_1
   19:  aload_1
   20:  areturn

So, a += b is the equivalent of

a = new StringBuilder()
    .append(a)
    .append(b)
    .toString();

The concat method should be faster. However, with more strings the StringBuilder method wins, at least in terms of performance.

The source code of String and StringBuilder (and its package-private base class) is available in src.zip of the Sun JDK. You can see that you are building up a char array (resizing as necessary) and then throwing it away when you create the final String. In practice memory allocation is surprisingly fast.

Update: As Pawel Adamski notes, performance has changed in more recent HotSpot. javac still produces exactly the same code, but the bytecode compiler cheats. Simple testing entirely fails because the entire body of code is thrown away. Summing System.identityHashCode (not String.hashCode) shows the StringBuffer code has a slight advantage. Subject to change when the next update is released, or if you use a different JVM. From @lukaseder, a list of HotSpot JVM intrinsics.

Concat strings in SQL Server and Oracle with the same unmodified query

If you want to go down the path of creating a stored procedure, whatever framework you're using should be able to more or less transparently handle an Oracle stored procedure with an OUT parameter that is a SYS_REFCURSOR and call that as you would a SQL Server stored procedure that just does a SELECT statement.

CREATE OR REPLACE PROCEDURE some_procedure( p_rc OUT sys_refcursor )
AS
BEGIN
  -- You could use the CONCAT function rather than Oracle's string concatenation
  -- operator || but I would prefer the double pipes.
  OPEN p_rc
   FOR SELECT column1 || ' - ' || column2
         FROM myTable;
END;

Alternatively, you could define your own CONCAT function in SQL Server.

Concatenation operator (+) vs. concat()

The concat method always produces a new String with the result of concatenation.

The plus operator is backed by StringBuilder creation, appending all String values you need and further toString() calling on it.

So, if you need to concatenate two values, concat() will be better choice. If you need to concatenate 100 values, you should use the plus operator or explicitly use StringBuilder (e.g. in case of appending in a cycle).

---

Related Topics