Solved

How to merge columns data into single CLOB column

Posted on 2003-11-01
6
1,584 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:nougain
  • 2
6 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 50 total points
ID: 9663333
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.

0
 

Expert Comment

by:javaq092999
ID: 9669584
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
0
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 50 total points
ID: 9670112
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:

begin
  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?
end;
/

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!
0
 

Expert Comment

by:javaq092999
ID: 9670334
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now