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

3 comments:

  1. A valuable post.
    I recommend using DBMS_UTILITY.GET_TIME for execution time between two points.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. I noticed that for the example 2,
    if you use:
    my_clob := my_clob || to_clob(i || ',');
    instead of
    my_clob := my_clob || to_clob(i) || ',';
    it will perform the same as dbms_lob.append.
    BUT:
    If you use
    my_clob := my_clob || to_clob(i) || to_clob(',');
    then it is just as bad as the one without conversion to_clob.

    ReplyDelete