asked on

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,
TEXT CLOB);

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...

<SEARCH_DATA>
<C1>...data...</C1><C2>...data...</C2><C3>...data...</C3><C4>...data...</C4>
</SEARCH_DATA>

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).

declare
v_T1_ID T1.T1_ID%TYPE;
v_TITLE T1.TITLE%TYPE; -- VARCHAR2(100)
v_DESCRIPTION T1.DESCRIPTION%TYPE; -- CLOB
searchdata CLOB;

CURSOR curs is
SELECT
T1_ID,
TITLE,
DESCRIPTION
FROM
T1
ORDER BY
T1_ID;
begin
open curs;
loop
fetch curs into
v_T1_ID,
v_TITLE,
v_DESCRIPTION;

exit when curs%NOTFOUND;

dbms_output.put_line(v_T1_ID);

searchdata :=
'<SEARCH_DATA>'
|| '<TITLE>' || NVL(v_TITLE, '') || '</TITLE>'
|| '<DESCRIPTION>' || NVL(v_DESCRIPTION, '') || '</DESCRIPTION>'
||
'</SEARCH_DATA>';

insert into SEARCH_TABLE VALUES(v_T1_ID, searchdata);
end loop;
close curs;
commit;
end;
/

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.

Thanks

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

javaq092999

seazodiac,

Thanks for your resposne. I agree on most of the points but...

Ans to Your point 1: Yes it is duplication of data in a sense and yes more than double due to those XML tags. I agree. But mearging them all into a single CLOB field will make the things easy in writing the Application (user interface, Java).

Ans to Your point 2: I planned for "AFTER INSERT OR UPDATE" trigger to update the SEARCH_TABLE when any of transactional table is updated/inserted. Though I have a problem in using the :new on CLOB fields in the trigger. I still have to find solution to that.

Ans to Your point 3: You are write. I have planned to change them using DBMS_LOB.write() and DBMS_LOB.writeappend() but at this moment don't know how to use them.

Ans to Your point 4: Since Oracle9iR2 has provided XML support in "Oracle Text (aka InterMedia)" using the WITHIN predicate it becomes easy to search the selective XML nodes in the data I am storing CLOB field. Also the index generated on the CLOB field such that each XML node gets indexed making it even faster. I tested this with some 7000 (XML formatted data in CLOB) records and found the search very fast.

But still I have many issues in hand and wondering if this is the right way of doing. If someone else has used this approach it would be nice to hear their experience.

> I think Search the text in the all individual tables is not that bad
> as long as it's properly indexes and tuned.

If you could give some idea in this direction it will be a help.

Thanks

SOLUTION

andrewst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

javaq092999

andrewst,

Yes! the intention is to allow a user to search for some text (words, like google) across all varchar2 and CLOB fields of all 10 tables. Also in advance search version user should be allowed to select which tables he wants to search and which columns in each table he wants to search.

I will try your suggestion. Looks like a very viable one. It will eleminate many of the issues I have (due to lack of knowledge) in the XML based search. Only issue remaining is the duplication of data but that is acceptable to me.

(Just to correct one statement from you ... I do not use or intend to use the 'Java to search that XML content'. Yes it would be horrible to do that. However Oracle Text Index does provide the fascility of searching XML data -- WITHIN, xmlgroup etc, indexing at each xml node). .... But I liked your idea. You have suggested the same thing in SIMPLIFIED form.

Thanks