Re: Update of Clobs *Performance*

Mark,
Just catching up with email. Certainly the issues other folks have mentioned (such as indexes) come into play. On the developer side, I'd be wary of bringing a clob into a varchar2 unless you know it's always going to be less than 32,500 bytes (per your example below). DBMS_LOB has a copy feature, which theoretically should be faster, and "more appropriate". There are many ways of meeting your requirements: I enclose a sample script below which uses the bulk collect feature. This script works on 10g given your sample code, but I've added no error handling etc., but feel free to try it out and let me know.

Regards,
Richard.

Richard J Stevenson
CobbleSoft International Ltd.
www.cobblesoft.com
US/Can Toll-Free: 866-380-6716
International: +1 315 548 5810

declare
 cursor c1 is
 select taba_clob
  ,tabb_clob
 from table_a a,
  table_b b
 where b.tabb_num = to_number(a.taba_char) for update;

 type myclob is table of clob;

 mysource myclob;
 mydest myclob;
begin

open c1;
fetch c1 bulk collect into mysource,mydest; close c1;

for i in mysource.first..mysource.last loop  dbms_lob.copy(mydest(i), mysource(i), dbms_lob.getlength(mysource(i))); end loop;
commit;
end;
/

• Original Message ----- From: Mike Schmitt To: oracle-l_at_freelists.org Sent: November 03, 2004 14:28 Subject: Update of Clobs *Performance*

  Hi all,

  I have a developer who is trying to use PL/SQL to update all of the CLOBS   of a specific table (nightly basis). I am looking for advice on how to   speed up the performance for this process. SQL tracing the process shows   the following before I cancel out.

  call count cpu elapsed disk query current   rows

• ------ -------- ---------- ---------- ----------
  • ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 19913 5116.93 5266.76 99893046 101038562 3 1 Fetch 0 0.00 0.00 0 0 0 0
• ------ -------- ---------- ---------- ----------
  • ---------- total 19914 5116.93 5266.76 99893046 101038562 3 1

  Thanks

  Table_A (141,000 rows, no indexes)
  tabA_char VARCHAR2(10)
  tabA_clob CLOB

  Table_B (145,000 rows, no indexes)
  tabB_num number
  tabB_clob CLOB

  Procedure
  declare
  v_clob varchar2(32500);
  v_id varchar(10);

  cursor cont_rep_clob is
  select tabA_char, tabA_clob
  from Table_A;

  begin
  open cont_rep_clob;
  loop
  fetch cont_rep_clob into v_id, v_clob;

  exit when cont_rep_clob%NOTFOUND;

  update Table_B
  set tabB_clob = v_clob
  where to_char(tabB_num) = v_id;

  commit;

  end loop;
  close cont_rep_clob;

--

  http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l