RE: Update of Clobs *Performance*
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Nov 2004 15:00:03 -0500
create index why_full_scan_all_my_clobs_for_each_one_row_update on
tableB(tabB_num)
change your where clause to where tabB_num = to_number(v_id)
Think about a commit counter within the loop less than the entire table.
Maybe 1000 or 10000?
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mike Schmitt
Sent: Wednesday, November 03, 2004 2:29 PM
To: oracle-l@xxxxxxxxxxxxx
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:
