ajexpert
asked on
Concate operation: How to optimize
I want to know how to optimize concat operation for CLOB datatype.
We have a package, which loops through 100,000 records (or more) does lot of validations and again concates with other clob element.
Inside CURSOR loop, we do concatination of the previous elements to the current element.
When I executed the package without concatenation, it executated within minute, but with concat operation it took 30 minutes.
This concatenated string(clob) cannot be converted into temp table or associative array as this returned as CLOB to front end.
Is there any way to optimize the concatenation which results in forming a clob?
We have a package, which loops through 100,000 records (or more) does lot of validations and again concates with other clob element.
Inside CURSOR loop, we do concatination of the previous elements to the current element.
When I executed the package without concatenation, it executated within minute, but with concat operation it took 30 minutes.
This concatenated string(clob) cannot be converted into temp table or associative array as this returned as CLOB to front end.
Is there any way to optimize the concatenation which results in forming a clob?
ASKER
--One of scenario (mocked up) that we use is
If I remove the concatenation operation, it performs faster
If I remove the concatenation operation, it performs faster
/* Formatted on 5/7/2011 12:06:32 AM (QP5 v5.115.810.9015) */
DECLARE
lv_col_sids CLOB;
lv_sid NUMBER;
CURSOR C1
IS
SELECT t1_sid, t2_name, t3_value FROM tableT;
BEGIN
FOR C1_REC IN C1 -- approx 100,000 or more
LOOP
-- lot of checks -- IF ... ELSE
-- REF CURSOR's ---
-- open ref cursor
lv_col_sids := lv_col_sids || ',' || c1_rec.t1_sid; -- ** THIS CONCATENATION IS BOTTLE NECK
-- close ref cursor
END LOOP;
lv_col_sids := LTRIM (lv_col_sids, ',');
END;
I agree with slightwv, but you might still try to modify frontend to accept row piped clobs to ease up the oracle load...
It depends, of course, what exactly are you doing with returned clob... and what is the average size of returning clob...
Regards
It depends, of course, what exactly are you doing with returned clob... and what is the average size of returning clob...
Regards
Have good news I hope: I have two options that CRUSH your concat on my little XE database. I only used 10,000 rows but it should scale to your 100,000+ rows.
In my testing, DBMS_LOB wins hands down but only slightly over the XML trick.
Hopefully your testing will show the same results.
In my testing, DBMS_LOB wins hands down but only slightly over the XML trick.
Hopefully your testing will show the same results.
drop table tab1 purge;
create table tab1(t1_sid char(10));
begin
for i in 1..10000 loop
insert into tab1 values('1234567890');
end loop;
end;
/
commit;
DECLARE
lv_col_sids CLOB;
start_time timestamp;
end_time timestamp;
CURSOR C1
IS
SELECT t1_sid FROM tab1;
BEGIN
-- Original string concat
start_time := systimestamp;
dbms_lob.createtemporary(lv_col_sids,true);
dbms_lob.open(lv_col_sids, dbms_lob.lob_readwrite);
FOR C1_REC IN C1 -- approx 100,000 or more
LOOP
lv_col_sids := lv_col_sids || ',' || c1_rec.t1_sid; -- ** THIS CONCATENATION IS BOTTLE NECK
END LOOP;
lv_col_sids := LTRIM (lv_col_sids, ',');
end_time := systimestamp;
dbms_output.put_line('Time: ' || to_char(end_time-start_time));
dbms_lob.freetemporary(lv_col_sids);
-- DBMS_LOB.WRITEAPPEND
start_time := systimestamp;
dbms_lob.createtemporary(lv_col_sids,true);
dbms_lob.open(lv_col_sids, dbms_lob.lob_readwrite);
FOR C1_REC IN C1 -- approx 100,000 or more
LOOP
dbms_lob.writeappend(lv_col_sids, length(c1_rec.t1_sid), c1_rec.t1_sid);
END LOOP;
lv_col_sids := LTRIM (lv_col_sids, ',');
end_time := systimestamp;
dbms_output.put_line('Time: ' || to_char(end_time-start_time));
dbms_lob.freetemporary(lv_col_sids);
-- XMLAGG trick
start_time := systimestamp;
SELECT
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", t1_sid || ',')), '/s/text()').getclobval(),
','
) into lv_col_sids
FROM tab1;
end_time := systimestamp;
dbms_output.put_line('Time: ' || to_char(end_time-start_time));
dbms_lob.freetemporary(lv_col_sids);
END;
/
ASKER
@slightwv:
I tried XMLAGG trick, it is very much faster than string concat operation, However I hit another roadblock when I try to save the clob into table.
This is the error when I try to save into table having clob column
ORA-22993: specified input amount is greater than actual source amount
I tried XMLAGG trick, it is very much faster than string concat operation, However I hit another roadblock when I try to save the clob into table.
This is the error when I try to save into table having clob column
ORA-22993: specified input amount is greater than actual source amount
-- table for storing clob data
create table tmp_store_clob(str_clob CLOB);
--------------------------------------------------
DECLARE
lv_col_sids CLOB;
BEGIN
SELECT
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", t1_sid || ',')), '/s/text()').getclobval(),
','
) into lv_col_sids
FROM tab1;
-- this gives error ORA-22993
INSERT INTO tmp_store_clob(lv_col_sids);
END;
As posted, the insert causes a syntax error. I assume this is because you posted a scrubbed version of the code?
The code below runs for me as-is and produces:
SQL>select length(str_clob) from tmp_store_clob;
LENGTH(STR_CLOB)
----------------
109999
The code below runs for me as-is and produces:
SQL>select length(str_clob) from tmp_store_clob;
LENGTH(STR_CLOB)
----------------
109999
drop table tab1 purge;
create table tab1(t1_sid char(10));
begin
for i in 1..10000 loop
insert into tab1 values('1234567890');
end loop;
end;
/
commit;
-- table for storing clob data
drop table tmp_store_clob purge;
create table tmp_store_clob(str_clob CLOB);
--------------------------------------------------
DECLARE
lv_col_sids CLOB;
BEGIN
SELECT
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", t1_sid || ',')), '/s/text()').getclobval(),
','
) into lv_col_sids
FROM tab1;
-- this gives error ORA-22993
INSERT INTO tmp_store_clob values(lv_col_sids);
END;
/
select length(str_clob) from tmp_store_clob;
LENGTH(STR_CLOB)
----------------
109999
ASKER
I checked the length of lv_col_sids in tab1 table, after XMLAGG it is 396988
Please check the example, its mockup and excerpt of my pl/sql
Please check the example, its mockup and excerpt of my pl/sql
drop table tab1 purge;
create table tab1(t1_sid NUMBER);
begin
for i in 1..30000 loop
insert into tab1 values(123456789012345);
end loop;
end;
/
commit;
-- table for storing clob data
drop table tmp_store_clob purge;
create table tmp_store_clob(str_clob CLOB);
DECLARE
lv_col_sids CLOB;
BEGIN
EXECUTE IMMEDIATE ' TRUNCATE TABLE tmp_store_clob';
SELECT
RTRIM(
EXTRACT(XMLAGG(XMLELEMENT("s", t1_sid || ',')), '/s/text()').getclobval(),
','
) into lv_col_sids
FROM tab1;
-- this gives error ORA-22993
INSERT INTO tmp_store_clob values(lv_col_sids);
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks slightwv. I really appreciate your efforts.
I am looking into this as well as other issues which have gained more priority today, will update you within this week.
Thanks a lot once again slightwv
I am looking into this as well as other issues which have gained more priority today, will update you within this week.
Thanks a lot once again slightwv
No problem. Take your time. You know how to get me if you need me!
ASKER
Thanks Slightwv!!
Can you provide a test case where we can play with different scenarios?