RE: Update of Clobs *Performance*

Mike,
check the explain plan for the update. the "where to_char(tabB_num) =3D = v_id" is generally a non-indexable predicate; which may be causing a FTS = of the table for each update. "vi_id" should be the same data_type as = "tabB_num", resolve this before the update and see if that helps...

Bruce McCartney
Database Information System Inc
bruce.mccartney_at_dbinfosystems.com
403.615.3350

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mike Schmitt Sent: Wednesday, November 03, 2004 12: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

• ---------- Parse 1 0.00 0.00 0 0 0=20 0 Execute 19913 5116.93 5266.76 99893046 101038562 3=20 1 Fetch 0 0.00 0.00 0 0 0=20 0
  ------- ------ -------- ---------- ---------- ----------=20
  
• ---------- total 19914 5116.93 5266.76 99893046 101038562 3=20 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 =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:55:08 CST