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

You should have included a DBMS_LOB.writeappend in addition to the DBMS_LOB.append. They are much faster (at least on 11.2) as the LOB.append does conversion of charbuffer to CLOB first (creating an additional temporary CLOB) and then copies this CLOB contents to the main CLOB which is much less efficent than instruct Oracle to directly copy content of char buffer to end of CLOB.

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