Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1000
  • Last Modified:

CLOB update performance

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
diannagibbs
Asked:
diannagibbs
  • 2
  • 2
1 Solution
 
sdstuberCommented:
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
 
diannagibbsAuthor Commented:
Unfortunately, this is increasing my time by 3.  This is a development database with small SGA.  Very challenging.
0
 
sdstuberCommented:
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
 
diannagibbsAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now