| Query takes long time -need help to improve Performance [message #118649] |
Fri, 06 May 2005 13:20  |
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
|
Senior Member |
|
|
I have written a simple procedure which takes parameter of passing range of IDs to the select statement. Records selected are read sequentially, then do the conversion from string of text to CLOB format. And update to the selected record with converted CLOB.
Below is my query.
procedure CORRESP_TEXT_PKG6_2_XML(p_low_correspondence_id NUMBER, p_high_correspondence_id NUMBER)
--*
--* Converts RLN and SLN corresp_type letters CORRESP_DATA.text column to XML format.
--*
is
cursor CORRESP_LOOKUP
is
select cd.corresp_data_id,
cd.text,
cc.request_date,
cc.correspondence_id,
cc.STREET,
cc.STREET2,
cc.CITY,
cc.STATE_CODE,
cc.ZIPCODE,
cc.CLIENT_SERVICE_ID,
cc.CORRESP_TYPE
from CORRESP_DATA cd, CORRESPONDENCE cc
where cd.correspondence_id = cc.correspondence_id
and cc.corresp_type in ('RLN', 'SLN' )
and cd.corresp_element_no in (229, 236, 240)
and text NOT LIKE '<%'
and cc.correspondence_id between p_low_correspondence_id and p_high_correspondence_id
order by cc.CORRESP_TYPE, cd.CORRESPONDENCE_ID, cd.corresp_element_no asc
;
I put the above result in a curor and read them for conversion from text to CLOB.
And I do the update using the below SQL.
procedure UPD_CORR_DATA(l_xmltext VARCHAR2, v_corresp_data_id NUMBER)
--
--* Converts CORRESP_DATA.text column to XML format.
--*
is
l_XMLCLOB CLOB;
v_xml_text varchar2(32000);
begin
v_xml_text := l_xmltext || '</letter>';
DBMS_LOB.createtemporary(l_XMLCLOB, TRUE);
DBMS_LOB.WRITE(l_XMLCLOB, LENGTH(v_xml_text), 1, v_xml_text);
update CORRESP_DATA
set text = l_XMLCLOB
where corresp_data_id = v_corresp_data_id
;
i_TmpCnt := i_TmpCnt + SQL%rowcount;
if i_TmpCnt >= 1000
then
commit;
i_TmpCnt := 0;
end if;
end UPD_CORR_DATA;
When tested for 100 records it takes less than 5 seconds. But when I increase the range, it takes long time and SQL errors out with below
'ORA-1652: Unable to extend temp segments by 5 tablespace TEMP'. I added a new datafile to the TEMP tablespace with a size of 1024M. Even then it takes a long time to process. I have about 15 million records that need to be converted and update to the table.
Any help on this is greatly appreciated.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
