The fastest way of string concatenation in Oracle PL/SQL

00_conclusions.md

When builing large strings with lots of concatenations in PL/SQL, custom approach like "use varchar2(32000) buffer to reduce the number of dbms_lob calls" makes sense even for Oracle 12.1.0.2.0.

The response time improvement is 4..300+ times :) depending on the DB version and API you use.

Note that simple v_clob || TO_CHAR (SYSTIMESTAMP) || ', ' ==> v_clob || TO_CLOB(TO_CHAR (SYSTIMESTAMP) || ', ') trick (note the extra to_clob) makes 24 times improvement for 11g. The trick is bad for 12c where results in 1.5x degradation.

The code was published 4 year ago as an answer to "How to Quickly Append VARCHAR2 to CLOB": http://www.talkapex.com/2009/06/how-to-quickly-append-varchar2-to-clob.html?showComment=1343235921606#c9077980873875311325

Gist formating makes the answer cleaner.

Updates:

18 July 2018: added dbms_lob.writeappend as it is faster in both 11g and 12c. Note that app results are not improved much since the number of dbms_lob calls there is low.

20 March 2024: added Oracle 19.9

01_results.txt

The test runs 10000 iterations and ends up with 380'000 characters long clob.

11g Release 11.2.0.4.0 - 64bit Production:
1st execution
CLOB := CLOB || VARCHAR2 approach:          00:00:37.35
CLOB := CLOB || TO_CLOB(VARCHAR2) approach: 00:00:01.69
CLOB := CLOB || TMP_CLOB approach:          00:00:01.68
app(v_clob, v_vc, VARCHAR2) approach:       00:00:00.11 <== Winner
DBMS_LOB.append approach:                   00:00:01.58
DBMS_LOB.writeappend approach:              00:00:00.49

2nd execution (just to show signal to noise level)
CLOB := CLOB || VARCHAR2 approach:          00:00:43.80
CLOB := CLOB || TO_CLOB(VARCHAR2) approach: 00:00:01.65
CLOB := CLOB || TMP_CLOB approach:          00:00:01.61
app(v_clob, v_vc, VARCHAR2) approach:       00:00:00.11 <== Winner
DBMS_LOB.append approach:                   00:00:01.68
DBMS_LOB.writeappend approach:              00:00:00.46

12c Release 12.1.0.2.0 - 64bit Production:
1st execution
CLOB := CLOB || VARCHAR2 approach:          00:00:00.924
CLOB := CLOB || TO_CLOB(VARCHAR2) approach: 00:00:00.677
CLOB := CLOB || TMP_CLOB approach:          00:00:00.696
app(v_clob, v_vc, VARCHAR2) approach:       00:00:00.112 <== Winner
DBMS_LOB.append approach:                   00:00:00.720
DBMS_LOB.writeappend approach:              00:00:00.435

2nd execution (just to show signal to noise level)
CLOB := CLOB || VARCHAR2 approach:          00:00:00.929
CLOB := CLOB || TO_CLOB(VARCHAR2) approach: 00:00:00.739
CLOB := CLOB || TMP_CLOB approach:          00:00:00.717
app(v_clob, v_vc, VARCHAR2) approach:       00:00:00.111 <== Winner
DBMS_LOB.append approach:                   00:00:00.717
DBMS_LOB.writeappend approach:              00:00:00.453

Oracle Database 19c Standard Edition 2 Release 19.9.0.0.0 - Production
1st execution

CLOB := CLOB || VARCHAR2 approach:          00:00:00.570737000
CLOB := CLOB || TO_CLOB(VARCHAR2) approach: 00:00:00.416159000
CLOB := CLOB || TMP_CLOB approach:          00:00:00.445300000
app(v_clob, v_vc, VARCHAR2) approach:       00:00:00.059103000 <== Winner
DBMS_LOB.append approach:                   00:00:00.435128000
DBMS_LOB.writeappend approach:              00:00:00.313811000

2nd execution (just to show signal to noise level)

CLOB := CLOB || VARCHAR2 approach:          00:00:00.603622000
CLOB := CLOB || TO_CLOB(VARCHAR2) approach: 00:00:00.448007000
CLOB := CLOB || TMP_CLOB approach:          00:00:00.426496000
app(v_clob, v_vc, VARCHAR2) approach:       00:00:00.056168000 <== Winner
DBMS_LOB.append approach:                   00:00:00.433901000
DBMS_LOB.writeappend approach:              00:00:00.314912000

02_concatenate_test.sql

DECLARE
  v_start TIMESTAMP;
  v_end TIMESTAMP;
  v_clob CLOB;
  v_tmp_clob CLOB;
  v_iterations PLS_INTEGER := 10000; -- Used 1,000, 10,000, and 100,000 for testing
  v_vc varchar2(32000);
  v_len pls_integer := 0;
  procedure app(v_clob in out nocopy clob, v_vc in out nocopy varchar2, v_app varchar2) is
    begin
      v_vc := v_vc || v_app;
      exception when VALUE_ERROR then
      if v_clob is null then
        v_clob := v_vc;
      else
        dbms_lob.writeappend(v_clob, length(v_vc), v_vc);
      end if;
      v_vc := v_app;
    end;
BEGIN
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 approach:          ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) approach: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
    v_clob := v_clob || v_tmp_clob;
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB approach:          ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := null;

  FOR i IN 1 .. v_iterations LOOP
    app(v_clob, v_vc, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;
  v_clob := v_clob || v_vc;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('app(v_clob, v_vc, VARCHAR2) approach:       ' || TO_CHAR (v_end - v_start));

  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;

  FOR i IN 1 .. v_iterations LOOP
    DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('DBMS_LOB.append approach:                   ' || TO_CHAR (v_end - v_start));

  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;

  FOR i IN 1 .. v_iterations LOOP
    declare
      v varchar2(100) := TO_CHAR (SYSTIMESTAMP) || ', ';
    begin
      DBMS_LOB.writeappend (v_clob, length(v), v);
    end;
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('DBMS_LOB.writeappend approach:              ' || TO_CHAR (v_end - v_start));
END;
/

@Elias481 , thanks for the tip, I've added writeappend to the comparison matrix.