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

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


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_T1_ID                            T1.T1_ID%TYPE;
    v_TITLE                            T1.TITLE%TYPE;            -- VARCHAR2(100)
    v_DESCRIPTION                      T1.DESCRIPTION%TYPE;      -- CLOB
    searchdata                         CLOB;
    CURSOR curs is
        ORDER BY
    open curs;
         fetch curs into

         exit when curs%NOTFOUND;


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

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

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The right way to do this is NOT.

Have you thought of benefits by doing things this way? what do you gain?

Right upfront, I can see a few negatives:

1. Total waste of disk space (more than doule the original space due to extra XML tags).
2. Very hard if possible to maintain. How do you update the SEARCH_TABLE if any one of 10 transactional table get new data? remeber they are transactional tables.
3. Even with your working PL/SQL procedure, what if the string is above 32K? you have to use some other tricks to get around.
4. above all, Performance is worse than before. Besides, you have increase the overhead by adding xml tag, also By pulling all the individual columns into a huge CLOB, Which one you think it's better, short valued columns vs. a monstrous long text columns considering the data volume is the same.

Up to this point, I think you should turn around and rethink what you are trying  to do in the first place.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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.

Is the intention here to allow a user to search for some text across all columns of all 10 tables?  If so, then why not write to another table that looks like this:

create table search_table
( table_name varchar2(30) not null
, table_rowid rowid not null
, column_name varchar2(30) not null
, column_value clob

Now populate it like this:

  insert into search_table( table_name, table_rowid, column_name, column_value )
    select 'T1', t1.rowid, 'TITLE', t1.title from t1;
  insert into search_table( table_name, table_rowid, column_name, column_value )
    select 'T1', t1.rowid, 'DESCRIPTION', t1.description from t1;
  -- etc.  I presume there is no point in adding the ID, but you could?

Now all your application needs to do to seach is something like:

select * from search_table
where dbms_lob.instr( column_value, 'HELLO') > 0;

Oracle Text could be used to index the data for efficient searching.

A data searching solution that involves formatting all the data using XML and then searching that XML using Java seems a horribly inefficient approach, as seazodiac has already said.  Searching data is what databases are primarily for!

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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.