суббота, 10 декабря 2011 г.

Увеличиваем скорость выборки строк из таблицы с CLOB-полями

Есть в нашей БД такая табличка, которая содержит строки файлов (назовём таблицу FILE_LINES). Табличка используется для импорта данных из вне и последующей обработки их на стороне БД. И есть в этой таблице поле, назовём его TEXT, которое по типу своему текстовое. И сначала оно было VARCHAR2(1024) (ну думалось мне, что 1Кб хватит для текстового поля). Потом оно стало VARCHAR2(4000) (4000 – максимальный размер полей типа VARCHAR2 для Oracle). И, конечно же, настал момент, когда заказчик попросил это поле расширить  ещё на чуть-чуть…

Конечно же пришлось поменять тип поля TEXT на CLOB. И, в принципе, этого оказалось достаточно. Однако скорость загрузки строк из таблицы FILE_LINES на сторону клиентского приложения упала в разы (и это особо заметно при работе не в локальной сети, а с удалённой сетью заказчика). Конечно понятно, что выборка CLOB полей – это дополнительные расходы ресурсов памяти (как со стороны клиента + OCI.DLL, так и со стороны сервера) +  дополнительные обращения к серверу на выборку CLOB-значений. Ну и … пришлось с этим смириться – чтение этой таблицы клиентским приложением требуется только в случае, если обработка на стороне БД прошла с ошибками.

И всё же, эта ситуация мне не давала покоя, ведь тут как: значений, длина которых превышает 4000 символов меньше 1%, а ошибки обработки всё равно приходится разбирать мне, а значит от этих “тормозов” страдаю я сам.

Решение пришло как-то вдруг неожиданно и оказалось довольно простым. Вместо одного SELECT-запроса типа:

select <какие-то поля>, text from file_lines where <основное условие>

Выполнять последовательно два запроса: первый выбирает все строки, у которых длина поля TEXT меньше 4000, при этом делать приведение поля TEXT к VARCHAR2, второй – все остальные строки. Получилось примерно так:

-- 1й запрос:
select <какие-то поля>, to_char(text) text from file_lines where <основное условие> and nvl(length(text), 0) <= 4000

--2й запрос:
select <какие-то поля>, text from file_lines where <основное условие> and length(text) > 4000

 

Всё летает, я счастлив :с). Теперь осталось обхитрить импорт данных в БД, т.к. для CLOB значений вставка тоже проходит медленнее... А поскольку длинных значений очень мало, то стоит попробовать оптимизировать и это, например, разбив импорт в два этапа (правда тут с последовательностью строк может выйти казус, возможно этапов будет больше).

P.S.: Чтобы второй запрос не очистил набор данных, надо ухитриться каким-то образом настроить DataSet. Я давно с DataSet’ами не работал, и не знаю как это делается в общем случае.

P.P.S.: На самом деле у меня там несколько CLOB полей, просто в первом случае я в условии перечисляю их все через AND, а во втором через OR.

4 коммент.:

vsmihaylovsky комментирует...

Про датасет - имеется ввиду селекты?
Записать запросы через юнион не подходит в таком случае?

Николай Зверев комментирует...

Нет, тут фокус в том, что в первом запросе результирующий набор данных не содержит CLOB-поля, а второй - содержит.
Если мы запишем эти запросы через юнион (т.е. сведём всё к одному запросу), то Oracle попробует привести типы данных всех полей второго запроса к типам данных полей первого запроса... и скорее всего выдаст ошибку.
(Проверил, действительно ругается ORA-01790: expression must have same datatype as corresponding expression'.)

С DataSet'ами проблема может заключаться в том, что одному датасету нельзя два раза сказать Open (первый раз с первым запросом, второй раз - со вторым), не вызвав Close (что в общем случае очищает DataSet).
Ну и так же может быть проблема, что после выполнения первого запроса, DataSet запомнит типы полей и при попытке вызова второго запроса получится ерунда - CLOB-поля или вообще не выберутся, либо обрежутся до 4000 символов. Вобщем, это всё зависит от компонентов, которые вы используете для работы с Oracle.

Тим комментирует...

=) Круть. Даже не столько статья круть сколько коммент)

Tim комментирует...

кстати совпадение - если мышкой навести на дату в комменте то скрипт по ссылке зовется clob.html ))

Отправить комментарий

.

.