Append String to CLOB in Optimized way

In our Oracle programming life, we append lot of strings to CLOB. There could be various ways to append string to CLOB. Mostly programmers use simply concat operator (||) for this, but is it the optimized way to append String to a CLOB? In this post I am trying to execute some of the CLOB concat methods and test their performance. Lets go one by one.

1. Append String to CLOB using concat operator (||)

SQL> declare
  2    my_clob clob;
  3    my_time timestamp;
  4  begin
  5    my_time := systimestamp;
  6    for i in 1..100000
  7    loop
  8      my_clob := my_clob || to_char(i) || ',';
  9    end loop;
 10    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 11  end;
 12  /
Execution Time: +000000000 00:02:28.650000000

2. Append String to CLOB using concat operator (||) but after to_clob

SQL> declare
  2    my_clob clob;
  3    my_time timestamp;
  4  begin
  5    my_time := systimestamp;
  6    for i in 1..100000
  7    loop
  8      my_clob := my_clob || to_clob(i) || ',';
  9    end loop;
 10    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 11  end;
 12  /
Execution Time: +000000000 00:02:22.404000000

3. Append String to CLOB using concat operator (||) but using temporary varchar2 variable

SQL> declare
  2    my_clob clob;
  3    my_txt_temp varchar2(100);
  4    my_time timestamp;
  5  begin
  6    my_time := systimestamp;
  7    for i in 1..100000
  8    loop
  9      my_txt_temp := to_char(i) || ',';
 10      my_clob := my_clob || my_txt_temp;
 11    end loop;
 12    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 13  end;
 14  /
Execution Time: +000000000 00:00:18.951000000

4. Append String to CLOB using concat operator (||) but using temporary clob variable

SQL> declare
  2    my_clob clob;
  3    my_clob_temp clob;
  4    my_time timestamp;
  5  begin
  6    my_time := systimestamp;
  7    for i in 1..100000
  8    loop
  9      my_clob_temp := to_clob(i) || ',';
 10      my_clob := my_clob || my_clob_temp;
 11    end loop;
 12    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 13  end;
 14  /
Execution Time: +000000000 00:00:09.226000000

5. Append String to CLOB using concat operator (||) but using dbms_lob.append with to_char

SQL> declare
  2    my_clob clob;
  3    my_time timestamp;
  4  begin
  5    my_time := systimestamp;
  6    dbms_lob.createtemporary(my_clob, TRUE);
  7    for i in 1..100000
  8    loop
  9      dbms_lob.append(my_clob,to_char(i) || ',');
 10    end loop;
 11    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 12  end;
 13  /
Execution Time: +000000000 00:00:06.047000000

6. Append String to CLOB using concat operator (||) but using dbms_lob.append with to_clob

SQL> declare
  2    my_clob clob;
  3    my_time timestamp;
  4  begin
  5    my_time := systimestamp;
  6    dbms_lob.createtemporary(my_clob, TRUE);
  7    for i in 1..100000
  8    loop
  9      dbms_lob.append(my_clob,to_clob(i) || ',');
 10    end loop;
 11    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 12  end;
 13  /
Execution Time: +000000000 00:00:09.501000000

7. Append String to CLOB using concat operator (||) but using dbms_lob.append with varchar2 temporary variable

SQL> declare
  2    my_clob clob;
  3    my_txt_temp varchar2(100);
  4    my_time timestamp;
  5  begin
  6    my_time := systimestamp;
  7    dbms_lob.createtemporary(my_clob, TRUE);
  8    for i in 1..100000
  9    loop
 10      my_txt_temp := to_char(i) || ',';
 11      dbms_lob.append(my_clob,my_txt_temp);
 12    end loop;
 13    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 14  end;
 15  /
Execution Time: +000000000 00:00:06.065000000

8. Append String to CLOB using concat operator (||) but using dbms_lob.append with clob temporary variable

SQL> declare
  2    my_clob clob;
  3    my_clob_temp clob;
  4    my_time timestamp;
  5  begin
  6    my_time := systimestamp;
  7    dbms_lob.createtemporary(my_clob, TRUE);
  8    for i in 1..100000
  9    loop
 10      my_clob_temp := to_clob(i) || ',';
 11      dbms_lob.append(my_clob,my_clob_temp);
 12    end loop;
 13    dbms_output.put_line('Execution Time: ' || TO_CHAR(systimestamp - my_time));
 14  end;
 15  /
Execution Time: +000000000 00:00:12.377000000

What we learning from above 8 examples of appending string to CLOB ? There are 2 main leanings for me
1) dbms_lob.append is far more efficient than concat operator (||) when appending to CLOBs
2) Always be aware of Oracle feature designed to do a specific task, and use that :)

I hope that you have enjoyed this post. Comments are well appreciated...


Related Posts
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why my distinct query is not using the Index?
- Avoiding unnecessary function calls to optimize SQL statements
- Query optimization tips for Oracle
- How to Get Execution Plan and Statistics of Query
- Why prefer COALESCE over NVL
- ORA-06502: invalid LOB locator specified
- Alter VARCHAR2 Column To CLOB