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@xxxxxxxxxxxxx 
  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

Other related posts: