#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    6
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    6
    Rep Power
    0
    hedge,

    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    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:

    PHP Code:
    create table search_table as
    select id'<SEARCH_DATA>' || '<TITLE>' || NVL(TITLE'') || '</TITLE>' || '<DESCRIPTION>' || NVL(DESCRIPTION'') || '</DESCRIPTION>' || '</SEARCH_DATA>' as text
    from t1


IMN logo majestic logo threadwatch logo seochat tools logo