RE: Update of Clobs *Performance*
- From: Mike Schmitt <mschmitt@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 03 Nov 2004 15:19:29 -0600
Thank you all for the replies. As for some of the advice/questions asked.
Table A and Table B are almost identical with 1-1 matching(different types
atm) on the non-clob columns (That means there would be 140k updates
nightly if the developer has his way). I do plan on indexing these non-clob
columns, which I think will save a decent amount of time.
I was mainly curious if this is the best way to perform updates/DML on
CLOBS? I have never worked with CLOBS before. Is reading a CLOB into a
varchar2(32500), then writing it out to a CLOB the best way to do this? I
was hoping that there might be something within dbms_lob that makes this
easier.
Either way,
Thanks for the Input so far, except now I really want a Taco
At 03:01 PM 11/3/2004 -0500, Powell, Mark D wrote:
>Mike, how big is table B? And is there only one table B row per v_id?
>
>If table B is large and there is only one or very few matching rows for a
>v_id value then conversion of v_id into a char pl/sql variable and adding an
>index on tabB_num (or function based index to_char(tabB_num) if you want to
>use a numeric v_id) would at least eliminate the time spent full scanning B
>each and every time it is updated for A.
>
>HTH -- Mark D Powell --
>
>
>-----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:
