Is there performance difference between concat vs || in oracle

Question

I want to concat multiple(3) columns in oracle sql query. Currently I am using function concat. Someone suggested to use || in place of concat as it gives performance benefit.

Is it true? If yes why?

I see only benefit of || is that written query more readable.

Answer 1

I set up a simple PL/SQL script(below) to try both concatenation options within a loop 100 million times each. The result for || was 142.93 seconds and CONCAT was 144.11 seconds. Either way, you're talking about roughly 1.4 microseconds per operation. My conclusion is that there doesn't appear to be any appreciable performance difference.

In addition to being more readable, || is the ANSI standard for the concatenation operator.

---

DECLARE
   i NUMBER;
   j NUMBER := 100000000;
   v VARCHAR2 (1000);
   v_start TIMESTAMP := SYSTIMESTAMP;
BEGIN
   FOR i IN 1 .. j LOOP
      v := DBMS_RANDOM.VALUE () || DBMS_RANDOM.VALUE ();
   END LOOP;    
   DBMS_OUTPUT.put_line ('1: ' || (SYSTIMESTAMP - v_start));
END;

DECLARE
   i NUMBER;
   j NUMBER := 100000000;
   v VARCHAR2 (1000);
   v_start TIMESTAMP := SYSTIMESTAMP;
BEGIN
   FOR i IN 1 .. j LOOP
      v := CONCAT (DBMS_RANDOM.VALUE (), DBMS_RANDOM.VALUE ());
   END LOOP;    
   DBMS_OUTPUT.put_line ('2: ' || (SYSTIMESTAMP - v_start));
END;

---

As a footnote, Oracle says this about purpose of the CONCAT function:

When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle Database environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.

Answer 2

Both are just the same, CONCAT() is for supporting different Characters sets for SQL Script handling, where '||' might be interpreted wrongly.

From Documentation

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 4-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle Database environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.