Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Update of Clobs *Performance*
Mike,
when it comes to writes, the lob storage params are important.
Run your tests with the nologging as the storage params for the lobs.=20
The writes will be faster, potentially, much faster.
The reads will be slower as nologging =3D=3D nocache.
=20
-----Original Message-----
From: Mike Schmitt [mailto:mschmitt_at_uchicago.edu]=20
Sent: Wednesday, November 03, 2004 2:29 PM
To: oracle-l_at_freelists.org
Subject: Update of Clobs *Performance*
Hi all,
I have a developer who is trying to use PL/SQL to update all of the
CLOBS=20
of a specific table (nightly basis). I am looking for advice on how to=20
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=20
rows
------- ------ -------- ---------- ---------- ----------=20
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 =3D v_clob
where to_char(tabB_num) =3D v_id;
commit;
end loop;
close cont_rep_clob;
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 03 2004 - 13:51:29 CST
![]() |
![]() |