2

I have a table that I need to update:

create table test_tab(id number, first varchar2(100), second clob);
insert into test_tab values (1, 'john', 'kowalski');
insert into test_tab values (2, 'michael', 'surname');

Now, for every record in my table, I want to append a string to the clob field. I could use an usual concatenation operator:

update test_tab set second = second || 'some_string,';

And this works, but because my actual table is like 80k rows, the update process lasts for too long.

I'm thinking about using DBMS_LOB.APPEND(), but I don't know how to use it in UPDATE and if it's gonna help the performance.

Any ideas? Thanks in advance.

Jangcy
  • 553
  • 1
  • 6
  • 17
  • 1
    The data in the `second` column needs to be updated, for every record, and this work cannot be avoided. How long is "too long?" – Tim Biegeleisen Nov 16 '16 at 11:13
  • @TimBiegeleisen - The update for only one column lasts about 30 seconds. Sometimes I need to call the procedure for more than one column, so this time is multiplied. – Jangcy Nov 16 '16 at 11:35
  • I upvoted you, but I have never heard of a mass record update, certainly not one which would speed things up. – Tim Biegeleisen Nov 16 '16 at 11:36
  • [You can do this](http://stackoverflow.com/a/6572015/266304) but you'd need to test to see if it's any faster. [Also see this post](http://stackoverflow.com/q/10331912/266304) which compares performance of substring options; you might see similar variation, and it might depend on your version/platform potentially. – Alex Poole Nov 16 '16 at 11:55
  • I suppose the table is properly indexed.Using DBMS_LOB.APPEND() surely is a good approach, but this would need to happen in PL/SQL. Have a look here: http://www.talkapex.com/2009/06/how-to-quickly-append-varchar2-to-clob.html ... seems to account exactly for your problem! – sers Nov 16 '16 at 11:56

1 Answers1

1

When you need to update EVERY record in the table it is always faster to recreate table as select (CTAS). Regardless which method you are updating LOB column.

Example:

create table temp
as
select id, first, second||' some_string' as second
  from test_tab;

rename test_tab to old_test_tab; -- you can drop it later
rename temp to test_tab;

-- then you need to move all indexes, grants and etc from old_test_tab to test_tab;
Rusty
  • 1,988
  • 10
  • 12
  • Hey @Rusty. Thank you for your answer. Could you please precise what do you mean by 'recreate table'? Or maybe provide a little example of it? – Jangcy Nov 17 '16 at 14:28