asked on

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.

Sean Stuber

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)

diannagibbs

ASKER

Unfortunately, this is increasing my time by 3. This is a development database with small SGA. Very challenging.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

diannagibbs

ASKER

I'll try it again tomorrow. I finally was able to get the 1.3mill rows to complete but took 3 hours.