CLOB update performance
Posted on 2013-01-15
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.