November 1st, 2003, 08:44 AM
How to merge columns data into single CLOB column
Oracle9iR2 / Windows2000 Pro
There are 10 transactional tables having few columns as VARCHAR2 and few as CLOB. There are only 35000 records in total. For each record I have to generate a table named SEARCH_TABLE by concatenating the VARCHAR2 and CLOB fields data into one 'TEXT' CLOB column. The structure of SEARCH_TABLE is...
CREATE TABLE SEARCH_TABLE
(ID VARCHAR2(8) PRIMARY KEY,
The values are concatenated as a XML data. So if I have say 4 columns (C1, C2, C3, C4) in Table T1 then the TEXT field of SEARCH_TABLE is polulated as...
To do this I have written the PL/SQL block that opens a cursor on the each table and does the concatenation of the fields including the XML tags (Column names).
v_TITLE T1.TITLE%TYPE; -- VARCHAR2(100)
v_DESCRIPTION T1.DESCRIPTION%TYPE; -- CLOB
CURSOR curs is
fetch curs into
exit when curs%NOTFOUND;
|| '<TITLE>' || NVL(v_TITLE, '') || '</TITLE>'
|| '<DESCRIPTION>' || NVL(v_DESCRIPTION, '') || '</DESCRIPTION>'
insert into SEARCH_TABLE VALUES(v_T1_ID, searchdata);
Problem is that it takes lot of time to do the population. After some investigation I found that more the fields I have to concatenate the more time it takes (and also many XML tags I concatenate, two for each column). For 1500 records and 6 columns it took me 11 minutes. Not good.
Is String Concatenation in Oracle is costly (something similar to Java String Vs StringBuffer). What is the right way of doing this.
November 1st, 2003, 01:16 PM
Not sure what's making that so slow but I wonder if you have tried it without pl/sql.
with what you're doing that could be done with a simple insert..select statement, I wonder if that would be any faster.
November 2nd, 2003, 08:01 PM
Thanks for your response. No I have not tried with the single non pl/sql command. Could you please help how to write that single command that does what I am trying.
Incidently for another table of mine having 5500 records and 8 VARCHAR2 and CLOB fields it took 2 Hrs and 30 Mins. Just not acceptable.
Just to give you one observation is that when I login into another SQL*Plus to check if the table is getting populating I come to know that the table gets populated much much earlier (eg 5 mins) but it took as much as 2:30 Hrs to get the SQL> prompt. Don't know what is going.
Please help. Thanks
November 3rd, 2003, 09:05 AM
I believe if you can do it as a create table as statement then redo logging will be disabled, this will probably increase performance as well:
create table search_table as
select id, '<SEARCH_DATA>' || '<TITLE>' || NVL(TITLE, '') || '</TITLE>' || '<DESCRIPTION>' || NVL(DESCRIPTION, '') || '</DESCRIPTION>' || '</SEARCH_DATA>' as text