LOB Performance..

[james777]

Hello All,

Below code hangs and coming out at all. I commented out the UPDATE part and did a dbmsout but even hanging for ever.

are there any performance problems..
declare
c clob;
type tt_id is table of vega.id%type;
type tt_val1 is table of vega.val1%type;
type tt_val2 is table of vega.val2%type;
type tt_row_id is table of rowid;
t_id tt_id;
t_val1 tt_val1;
t_val2 tt_val2;
t_row_id tt_row_id;

type rr is record
(id vega.id%type,
col varchar2(32000)
);

type tt_rr is table of rr index by pls_integer;
g_vega tt_rr;
v varchar2(32000);

begin

--for rec in ( select id, val1 from vega where mod(id,4) in (0,1) ) loop


-- Table vega got 100 rows ..
-- Lob is created inline ( enable stoare in row)

select rowid,id,val1,val2
bulk collect into t_row_id, t_id,t_val1,t_val2
from vega
where mod(id,4) in (0,1);

for i in t_row_id.first..t_row_id.last loop
dbms_output.put_line('Start: '||sysdate);
v := dbms_lob.substr(t_col_val(i),dbms_lob.getlength(t_val1(i)),1);
dbms_output.put_line('end: '||sysdate);
end loop;

/*
FOR j in t_row_id.first..t_row_id.last loop
update vega
set val2 = t_val1(j)
where rowid = t_row_id(j);
returning t_val1(j) into c;
end loop;
commit;
*/

exception
when others then
dbms_output.put_line(sqlerrm);
end;

 

[sem]

How many rows do you expect from that query? Why do you thing that you hang on your loop, not on select statement?

Regards, Dima

http://seminihin.narod.ru

 

[james777]

I expect 80 rows from the query. I have no idea why this is hanging forever. I am on 9iR2.
Basically i want to update and tried the for loop before updating.. Is it costly to convert the lob to variable (V). Any over head of creating temp lobs when convertion.


Thanks

 

[sem]

Did you try the same query without lobs, is it fast enough? What is an approximate lob size?

Regards, Dima

http://seminihin.narod.ru

 

[Wholsea]

You are running a select query to find all records, then you go and get those records again, then you loop a third time...

Your code may run more efficient if you create a cursor with the records you are looking to update and then update those records one at a time...

CURSOR csr_vega is
select rowid,id,val1,val2
from vega
where mod(id,4) in (0,1);


FOR rec_cols IN csr_vega LOOP
--do your data fixing and update here
end loop;

This will keep you from having to go through the tables multiple times. Find the records, update one record at a time...

I haven't done much with CLOBs but I assume this methodology would work with them...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise