Работа с CLOB, улучшение производительности

Этот пост может пригодиться, если работаем с LOB-объектам в базе данных Oracle.
Предыстория:
Есть динамически сформированные XML-документы, которые хранятся в Global Temporary Table (GTT), и которые хочется сконвертировать в CLOB и сохранить в базе.
Это реальный пример, документы – это MS Excel или MS Word документы, сформированные с использованием возможностей MS Office формировать документы с использованием WordML и XML Spreadsheet.

В основном, задача состоит в том, чтобы строки таблицы записать в одну результирующую.

Сейчас не рассматриваем возможности Oracle 11g

Итак, начальный вариант записи файлов в CLOB выгладел следующим образом:
Procedure Download_Result_Word(pi_store_id number) is
cur_clob CLOB;
Begin
for d_all in (select distinct file_name, file_ext,
decode(lower(file_ext), ‘xml’, ‘doc’, file_ext) file_ext_file
from STD_WORD_API_FILE
order by file_name, file_ext
)
loop
insert into MPD_DOG_FILE(id, dfs_id, file_name, file_ext, file_type, file_content)
values(MPD_DFL_SEQ1.Nextval, pi_store_id, d_all.file_name, d_all.file_ext_file, ‘W’, EMPTY_CLOB())
returning file_content into cur_clob;

if DBMS_LOB.isopen(cur_clob)=0
then
DBMS_LOB.open(cur_clob, DBMS_LOB.lob_readwrite);
end if;

for w in (select line_number, line_value
from STD_WORD_API_FILE
where file_name = d_all.file_name
and file_ext = d_all.file_ext
order by line_number
)
loop
if w.line_value is not null
then
DBMS_LOB.writeappend(cur_clob, DBMS_LOB.getlength(w.line_value), w.line_value);
end if;
end loop;

if DBMS_LOB.isopen(cur_clob)=1
then
DBMS_LOB.Close(cur_clob);
end if;
end loop;
End;

Однако, как только добавился вызов DBMS_LOB.writeappend сразу же упала производительность.

Т.е. например, если у нас есть 4 файла, и простое чтение данных составляет 1-2 секунды (если комментируем строку добавления к CLOB), то при выполнение процедуры с добавлением составило более 60 секунд.

Можно было бы и не использовать CLOB, а сделать копию структуры GTT и сохранять эти данные чтобы не иметь такой потери во времени.

Однако тут возможно увеличить производительность и получить то, что хотелось. Для этого дополнительно используем функциональность Temporary LOB

Переписываем процедуру следующим образом:

Procedure Download_Result_Word(pi_store_id number) is
cur_clob CLOB;

tmp_clob CLOB;
Begin
for d_all in (select distinct file_name, file_ext,
decode(lower(file_ext), ‘xml’, ‘doc’, file_ext) file_ext_file
from STD_WORD_API_FILE
order by file_name, file_ext
)
loop

DBMS_LOB.createtemporary(
lob_loc => tmp_clob,
cache => true,
dur => DBMS_LOB.session);

if DBMS_LOB.isopen(tmp_clob)=0
then
DBMS_LOB.open(tmp_clob, DBMS_LOB.lob_readwrite);
end if;

insert into MPD_DOG_FILE(id, dfs_id, file_name, file_ext, file_type, file_content)
values(MPD_DFL_SEQ1.Nextval, pi_store_id, d_all.file_name, d_all.file_ext_file, ‘W’, EMPTY_CLOB())
returning file_content into cur_clob;

if DBMS_LOB.isopen(cur_clob)=0
then
DBMS_LOB.open(cur_clob, DBMS_LOB.lob_readwrite);
end if;

for w in (select line_number, line_value
from STD_WORD_API_FILE
where file_name = d_all.file_name
and file_ext = d_all.file_ext
order by line_number
)
loop
if w.line_value is not null
then
DBMS_LOB.writeappend(tmp_clob, DBMS_LOB.getlength(w.line_value), w.line_value);
end if;
end loop;

DBMS_LOB.copy(
dest_lob => cur_clob,
src_lob => tmp_clob,
amount => DBMS_LOB.getlength(tmp_clob),
dest_offset => 1,
src_offset => 1);

if DBMS_LOB.isopen(cur_clob)=1
then
DBMS_LOB.Close(cur_clob);
end if;

if DBMS_LOB.isopen(tmp_clob)=1
then
DBMS_LOB.Close(tmp_clob);
end if;

DBMS_LOB.freetemporary(lob_loc => tmp_clob);
end loop;

End;

После чего мы имеем скорость выполнения 2-3 секунды (это приемлемое значение на сервере разработки с невысокой производительностью). Более того, данные лежат в CLOB и более удобно с ними работать.

Здесь мы сначала собираем весь документ в Temporary LOB, а потом копируем получившийся документ в результирующий CLOB.