Re: Update of Clobs *Performance*
- From: "Richard Stevenson" <rstevenson@xxxxxxxxxxxxxx>
- To: <mschmitt@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Nov 2004 11:57:56 -0500
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;
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
