Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

CLOB update performance

Posted on 2013-01-15
4
870 Views
Last Modified: 2013-04-04
I am updating a CLOB column from a varchar2 column in the same table.  My sql is:


update mytable a
SET ON_NARRATIVE_CLOB=(select RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", NARRATIVE||chr(32)) order by LINE), '/x/text()').getclobval(),',')
                                                from mytable b
                                                where a.column= b.column
                                                )

I have 1.2 million rows.  The update is very slow.  Any suggestions, comments, direction would be appreciated.  This is a one-time load for now and incrementals after this first load but I would like to get it as fast as possible.  Oracle 11.2.  Should I rewrite with PL/SQL?  Use a staging table?  Thanks.
0
Comment
Question by:diannagibbs
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38780249
try this....


CREATE OR REPLACE TYPE vcarray AS TABLE OF varchar2(4000);


CREATE OR REPLACE FUNCTION tbl2clob(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',')
    RETURN CLOB
    DETERMINISTIC
IS
    v_str    VARCHAR2(32767);
    v_clob   CLOB;
BEGIN
    DBMS_LOB.createtemporary(v_clob, TRUE);

    IF p_tbl.COUNT > 0
    THEN
        v_str  := p_tbl(1);

        FOR i IN 2 .. p_tbl.COUNT
        LOOP
            -- Appending to clobs is slower than appending to varchar2
            -- so use varchar2 until you can't anymore then append one big chunk

            v_str  := v_str || p_delimiter || p_tbl(i);

            IF LENGTH(v_str) > 28700
            THEN
                v_clob  := v_clob || v_str;
                v_str   := NULL;
            END IF;
        END LOOP;
    END IF;

    IF v_str IS NOT NULL
    THEN
        v_clob  := v_clob || v_str;
    END IF;

    RETURN v_clob;
END;
/


UPDATE mytable a
   SET on_narrative_clob      =
           (SELECT tbl2clob(CAST(COLLECT(b.narrative ORDER BY b.line) AS vcarray), ' ')
              FROM mytable b
             WHERE a.column = b.column)
0
 

Author Comment

by:diannagibbs
ID: 38782609
Unfortunately, this is increasing my time by 3.  This is a development database with small SGA.  Very challenging.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38782724
how odd,  the COLLECT method is consistently faster for me.

Also, as the data volume grows XMLAGG will eventually hit ORA-31186: Document contains too many nodes

and, if you happen to have any strings of 4000 characters the XMLAGG method will fail
with  ORA-01489: result of string concatenation is too long


Using COLLECT, these errors don't occur.
It's not only faster but, more importantly is able to finish whereas XMLAGG simply fails.
0
 

Author Comment

by:diannagibbs
ID: 38785383
I'll try it again tomorrow.  I finally was able to get the 1.3mill rows to complete but took 3 hours.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 87
data lookup in Oracle - need suggestions 55 121
database upgrade 8 111
How do I call MySQL Stored Procedure from oracle using HS link ? 5 58
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

860 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