Тема работы с большими объектами весьма объемна, поэтому мы не сможем рассмотреть все ее аспекты. Данную статью блога следует рассматривать как введение в программирование больших объектов для разработчиков PL/SQL. Мы познакомимся с некоторыми нюансами, которые необходимо учитывать, и рассмотрим примеры важнейших операций. Хочется надеяться, что представленный материал станет хорошей основой для ваших дальнейших исследований в этой области.
Прежде чем переходить к основному материалу, необходимо указать, что все примеры данного раздела основаны на следующем определении таблицы (см. файл source_code.sql):
TABLE waterfalls (
falls_name VARCHAR2(80),
falls_photo BLOB,
falls_directions CLOB,
falls_description NCLOB,
falls_web_page BFILE)
Рис. 1. Водопад Драйер-Хоуз возле Мунисинга (штат Мичиган)
Таблица содержит информацию о водопадах, расположенных в северной части штата Мичиган. На рис. 1 изображен водопад Драйер-Хоуз возле Мунисинга; в замерзшем состоянии его часто посещают альпинисты.
Каждый столбец таблицы предназначен для хранения больших объектов одного из четырех типов. Фотографии содержат большие объемы двоичных данных, поэтому для столбца falls_photo
column
определен тип BLOB
. Столбцы с информацией о местоположении и с описанием водопадов (falls_directions
и falls_descriptions
) содержат текст, поэтому они определены соответственно с типами CLOB
и NCLOB
. В реальной таблице они имели бы один и тот же тип, но мы хотели продемонстрировать в своем примере все разновидности больших объектов. Наконец, копия веб-страницы каждого водопада хранится в HTML-файле вне базы данных; в таблицу включен столбец типа BFILE
, содержащий указатели на эти файлы. Эти столбцы будут использоваться в последующих примерах для демонстрации различных аспектов работы с данными LOB
в программах PL/SQL.
При описании больших объектов мы будем часто использовать сокращение LOB
для обозначения всех описываемых типов данных — CLOB, BLOB, NCLOB
и BFILE
. При обсуждении конкретного типа будет приводиться его полное название.
Понятие локатора LOB
В работе с LOB
важнейшая роль отводится понятию локатора (locator
) LOB
. Локатором называется хранящийся в базе данных указатель на данные большого объекта. Давайте посмотрим, что же происходит при выборке данных из столбца типа BLOB
в переменную PL/SQL того же типа:
DECLARE
photo BLOB;
BEGIN
SELECT falls_photo
INTO photo
FROM waterfalls
WHERE falls_name='Dryer Hose';
Какое значение будет содержать переменная photo
после выполнения команды SELECT
? Графические данные изображения? Нет, в переменной будет храниться только указатель на двоичные данные. Результат выполнения команды SELECT
показан на рис. 2.
Рис. 2. Локатор LOB указывает на объект в базе данных
Этот механизм в корне отличается от того, как работают другие типы данных. Переменные и значения в столбцах LOB
содержат локаторы больших объектов, которые идентифицируют реальные данные, хранящиеся в другом месте базы данных или вне ее. Для работы с данными типа LOB
нужно сначала извлечь локатор, а затем с помощью встроенного пакета DBMS_LOB
получить и/или модифицировать реальные данные. Так, для получения двоичных данных фотографии, локатор которой хранится в столбце BLOB
из приведенной ранее таблицы, необходимо выполнить следующие действия:
- Выполнить команду
SELECT
и выбрать из базы данныхLOB
-локатор фотографии. - Открыть объект
LOB
с помощью функцииDBMS_LOB
.OPEN
. - Вызвать функцию
DBMS_LOB
.GETCHUNKSIZE
для получения оптимального (с точки зрения затрат на выполнение операции чтения или записи) размера фрагмента данных типаLOB
. - Вызвать функцию
DBMS_LOB
.GETLENGTH
для определения количества байтов или символов объектаLOB
. - Несколько раз вызвать функцию
DBMS_LOB
.GETLENGTH
для считывания данных типаLOB
. - Закрыть объект
LOB
.
Не все эти действия являются обязательными; не огорчайтесь, если что-то пока остается непонятным. Далее все эти операции будут описаны более подробно.
На первый взгляд кажется, что локаторы значительно усложняют схему работы с большими объектами. Однако предлагаемый Oracle подход имеет существенные преимущества, потому что он избавляет от необходимости возвращать все данные объекта LOB
при каждой выборке строки из таблицы. Представьте, сколько времени потребовалось бы для передачи 128 терабайт данных LOB
. Представьте, насколько это неэффективно, если вам нужна лишь небольшая часть этих данных. В механизме Oracle производится выборка локатора (быстрая операция), после чего читаются только те данные LOB
, которые вам нужны. Кроме того, объекты LOB
по умолчанию не кэшируются в буферном кэше, и для них не генерируются данные отмены (хотя генерируются данные повторного выполнения, если не установить параметр NOLOGGING
). Таким образом, загрузка 50 гигабайт данных LOB
не приведет к переполнению буферного кэша или таблиц отмены с общим снижением быстродействия. Раздельное кэширование и управление отменой LOB
было дополнительно усовершенствовано в Oracle11g... впрочем, об этом позднее.
LOB-ДАННЫЕ В ДОКУМЕНТАЦИИ ORACLE
Если вам часто приходится работать с большими объектами, настоятельно рекомендуем ознакомиться со следующими документами Oracle:
SecureFiles and Large Objects Developer’s Guide
— руководство по программированию с использованиемLOB
для Oracle11g и выше.Application Developer’s Guide — Large Objects
— руководство по программированию с использованиемLOB
для Oracle10g и более ранних версий.- PL/SQL
Packages and Types Reference
— глава с описанием пакетаDBMS_LOB
. - SQL Reference — раздел «
Datatypes
» главы 2 содержит важную информацию оLOB
-объектах.
Это далеко не полный список документации об объектах LOB
, но в этих документах можно найти все наиболее важные сведения.
Большие объекты — пустые и равные NULL
Теперь, когда вы понимаете различие между локатором LOB
и значением, на которое он ссылается, мы введем еще одно важное понятие: пустой объект LOB
. Так называется локатор, не указывающий ни на какие данные. Это не то же самое, что значение NULL
, то есть LOB
-столбец (или переменная), не содержащий локатора LOB
. Рассмотрим пример:
DECLARE
directions CLOB;
BEGIN
IF directions IS NULL THEN
DBMS_OUTPUT.PUT_LINE('directions is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('directions is not NULL');
END IF;
END;
directions is NULL
Объявленная нами переменная CLOB
содержит NULL
, потому что ей пока не присвоено никакое значение. Выглядит все привычно, не правда ли? Да и происходит то же самое, что при работе с данными любого другого типа: переменная объявлена без присваивания значения, и ее проверка на NULL
конструкцией IS NULL
дает результат TRUE
. В этом отношении данные LOB
напоминают объекты: перед добавлением данных они тоже должны быть инициализированы.
Давайте сделаем следующий шаг и инициализируем LOB
. В следующем коде вызов EMPTY_CLOB
инициализирует (но еще не заполняет!) переменную LOB
. Сначала программный код:
DECLARE
directions CLOB;
BEGIN
IF directions IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Сначала directions is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('Сначала directions is not NULL');
END IF;
DBMS_OUTPUT.PUT_LINE('Длина = '
|| DBMS_LOB.GETLENGTH(directions));
-- инициализация переменной LOB
directions := EMPTY_CLOB();
IF directions IS NULL THEN
DBMS_OUTPUT.PUT_LINE('После инициализации directions is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('После инициализации directions is not NULL');
END IF;
DBMS_OUTPUT.PUT_LINE('Длина = '
|| DBMS_LOB.GETLENGTH(directions));
END;
Результат:
Сначала directions is NULL
Длина =
После инициализации directions is not NULL
Длина = 0
Мы видим, что переменная CLOB
сначала представляет собой атомарное значение NULL
. Не удивительно, что длина NULL-LOB
тоже равна NULL
. После инициализации переменной типа CLOB
встроенной функцией EMPTY_CLOB
переменная уже не равна NULL
, потому что она содержит значение: локатор. Функция DBMS_LOB
.GETLENGTH
показывает, что даже инициализированная (NOT NULL
) переменная CLOB
остается пустой.
Очень важно понимать это различие, потому что у типов LOB
способ проверки наличия либо отсутствия данных получается более сложным, чем у скалярных типов. Для традиционных скалярных типов достаточно простой проверки IS NULL
:
IF some_number IS NULL THEN
-- Значит, данных нет
Если проверка IS NULL
для NUMBER
или VARCHAR2
(или любого другого скалярного типа) возвращает TRUE
, мы знаем, что переменная не содержит данных. Но при работе с LOB
необходимо проверить не только равенство NULL
(отсутствие локатора), но и длину:
IF some_clob IS NULL THEN
-- Данных нет
ELSIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN
-- Данных нет
ELSE
-- И только теперь данные присутствуют
END IF;
Итак, проверять нужно два условия вместо одного.
Для создания пустого объекта BLOB
используется функция EMPTY_BLOB
(). Для типов CLOB
и NCLOB
используется функция EMPTY_CLOB
().
Запись данных в объекты LOB
При наличии локатора данные записываются в LOB
-объект с помощью одной из процедур пакета DBMS_LOB
:
DBMS_LOB.WRITE
— запись в произвольную позицию.DBMS_LOB.WRITEAPPEND
— присоединение данных в конец LOB.
В приведенном ниже коде сначала создается локатор LOB
для столбца directions таблицы waterfalls
, а затем функция DBMS_LOB.WRITE
записывает данные в CLOB
-столбец строки «Munising Falls
». Функция DBMS_LOB.WRITEAPPEND
завершает дело:
/* Файл в Сети: munising_falls_01.sql */
DECLARE
directions CLOB;
amount BINARY_INTEGER;
offset INTEGER;
first_direction VARCHAR2(100);
more_directions VARCHAR2(500);
BEGIN
-- Удаление всех существующих строк со значением 'Munising Falls',
-- чтобы этот пример мог выполняться многократно.
DELETE
FROM waterfalls
WHERE falls_name='Munising Falls';
-- Вставка новой строки и вызов функции EMPTY_CLOB() для создания локатора LOB
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Munising Falls',EMPTY_CLOB());
-- Получение локатора LOB, созданного предыдущей инструкцией INSERT
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
-- Открытие объекта LOB; делать это необязательно, но лучше аккуратно
-- открывать и закрывать LOB-объекты
DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
-- Вызов DBMS_LOB.WRITE начинает запись.
first_direction := 'Follow I-75 across the Mackinac Bridge.';
amount := LENGTH(first_direction); -- количество записываемых символов
offset := 1; -- запись ведется с первого символа CLOB
DBMS_LOB.WRITE(directions, amount, offset, first_direction);
-- Вызов DBMS_LOB.WRITEAPPEND записывает дополнительные данные
more_directions := ' Take US-2 west from St. Ignace to Blaney Park.'
|| ' Turn north on M-77 and drive to Seney.'
|| ' From Seney, take M-28 west to Munising.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
-- И еще блок дополнительных данных
more_directions := ' In front of the paper mill, turn right on H-58.'
|| ' Follow H-58 to Washington Street. Veer left onto'
|| ' Washington Street. You''ll find the Munising'
|| ' Falls visitor center across from the hospital at'
|| ' the point where Washington Street becomes'
|| ' Sand Point Road.';
DBMS_LOB.WRITEAPPEND(directions,
LENGTH(more_directions), more_directions);
-- Закрываем объект LOB - работа выполнена.
DBMS_LOB.CLOSE(directions);
END;
В этом примере используются обе процедуры, WRITE
и WRITEAPPEND
. Впрочем, это сделано лишь в учебных целях — с таким же успехом можно было ограничиться WRITEAPPEND
, по-скольку объект изначально не содержит никаких данных. Обратите внимание на открытие и закрытие объекта CLOB
. Процедуры OPEN
и CLOSE
лучше вызывать явно, особенно при использовании Oracle Text. В противном случае создаваемые Oracle Text индексы будут обновляться при каждом вызове WRITE
или WRITEAPPEND
, а не один раз при вызове CLOSE
.
В разделе, посвященном типу данных BFILE
, будет рассказано, как прочитать данные LOB
прямо из внешнего файла операционной системы.
При записи данных в объекты LOB
обновлять LOB
-столбец в таблице не нужно, потому что локатор LOB
остается неизменным. Мы не изменяли содержимое falls_directions
(локатор) — вместо этого данные добавлялись в объект LOB
, на который ссылался локатор.
Обновление LOB
происходит в контексте транзакции. В данном примере мы не сохраняли результаты транзакции инструкцией COMMIT
. Однако если вы хотите, чтобы записанная в объект LOB
информация была сохранена в базе данных, после выполнения блока команд PL/SQL нужно выполнить инструкцию COMMIT
. Если же после блока команд PL/SQL будет выполнена инструкция ROLLBACK
, все произведенные в этом блоке изменения данных будут отменены.
В нашем примере данные записываются в столбец типа CLOB
. Данные BLOB
обрабатываются аналогично, с той лишь разницей, что аргументы процедур WRITE
и WRITEAPPEND
имеют тип данных RAW
, а не VARCHAR2
.
Следующий пример, реализованный в SQL*Plus, выводит на экран данные, помещенные ранее в таблицу waterfalls
. В следующем разделе будет показано, как извлекать данные с помощью разных процедур пакета DBMS_LOB
:
SQL> SET LONG 2000
SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
SQL> SELECT falls_directions
2 FROM waterfalls
3 WHERE falls_name='Munising Falls';
4 /
FALLS_DIRECTIONS
----------------------------------------------------------------------
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace
to Blaney Park. Turn north on M-77 and drive to Seney. From Seney,
take M-28 west to Munising. In front of the paper mill, turn right on
H-58. Follow H-58 to Washington Street. Veer left onto Washington
Street. You'll find the Munising Falls visitor center across from the
hospital at the point where Washington Street becomes Sand Point Road.
Чтение данных из объектов LOB
Данные читаются из LOB
при помощи процедуры DBMS_LOB
.READ
. Но сначала, конечно, следует получить локатор LOB
. При чтении данных типа CLOB
указывается смещение, задаваемое в символах. Операция чтения начинается с позиции, заданной смещением, а первый символ CLOB
всегда имеет номер 1. В случае применения типа данных BLOB
смещение задается в байтах. Кроме того, в вызове процедуры DBMS_LOB.READ
необходимо задать количество читаемых символов или байтов. Поскольку объекты LOB
бывают очень большими, имеет смысл извлекать их содержимое по частям.
Рассмотрим фрагмент кода, который загружает и выводит данные о местонахождении водопада Munising Falls
. Мы четко определяем количество читаемых символов, чтобы оно соответствовало ограничению на длину строки в пакете DBMS_OUTPUT
, а выводимый текст выглядел аккуратно.
/* Файл в Сети: munising_falls_02.sql */
DECLARE
directions CLOB;
directions_1 VARCHAR2(300);
directions_2 VARCHAR2(300);
chars_read_1 BINARY_INTEGER;
chars_read_2 BINARY_INTEGER;
offset INTEGER;
BEGIN
-- Получаем ранее добавленный локатор LOB
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Munising Falls';
-- Чтение начинается с первого символа
offset := 1;
-- Пытаемся прочитать 229 символов. В переменную chars_read_1
-- будет записано реальное количество прочитанных символов
chars_read_1 := 229;
DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);
-- Если прочитано 229 символов, информация о смещении обновляется
-- и производится попытка прочитать еще 255 символов.
IF chars_read_1 = 229 THEN
offset := offset + chars_read_1;
chars_read_2 := 255;
DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
ELSE
chars_read_2 := 0;
directions_2 := '';
END IF;
-- Вывод общего количества прочитанных символов
DBMS_OUTPUT.PUT_LINE('Characters read = ' ||
TO_CHAR(chars_read_1+chars_read_2));
-- вывод значения
DBMS_OUTPUT.PUT_LINE(directions_1);
DBMS_OUTPUT.PUT_LINE(directions_2);
END;
В результате выполнения этого кода выводится следующий текст:
Characters read = 414
Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignace to Blaney
Park. Turn north on M-77 and drive to Seney. From Seney, take M-28 west to
Munising. In front of the paper mill, turn right on H-58. Follow H-58 to
Washington Street. Veer left onto Washington Street. You'll find the Munising
Falls visitor center across from the hospital at the point where Washington
Street becomes Sand Point Road.
Процедуре DBMS_LOB.READ
в качестве второго параметра, объявленного как IN OUT
, передается переменная chars_read_1
(количество считанных символов). По окончании операции процедура обновляет его в соответствии с реальным количеством прочитанных символов. Если количество прочитанных символов (или байтов) будет меньше указанного, то это будет означать, что при чтении достигнут конец данных. К сожалению, обновить таким же образом информацию о смещении невозможно. Это приходится делать самостоятельно, по мере считывания последовательных фрагментов объектов LOB
, в зависимости от количества прочитанных символов.
Для определения размера большого объекта можно воспользоваться функцией DBMS_LOB
.GET_LENGTH
(локатор_lob
). Возвращаемое значение равно количеству байтов объекта BLOB
или BFILE
либо количеству символов объекта CLOB
.
Особенности типа BFILE
Как упоминалось ранее, типы данных BLOB, CLOB
и NCLOB
представляют внутренние большие объекты, хранящиеся в базе данных, в то время как BFILE
является внешним типом. Между объектами BFILE
и внутренними LOB
существуют три важных различия:
- Значение
BFILE
хранится не в базе данных, а в файле операционной системы. - Объекты
BFILE
не участвуют в транзакциях (то есть внесенные в них изменения нельзя ни отменить, ни сохранить). Однако изменения, вносимые в локаторBFILE
, в транзакциях можно и отменять, и сохранять. - И в PL/SQL, и в Oracle объекты
BFILE
можно только считывать. Запись данных через типаBFILE
не поддерживается. Внешние файлы, на которые указывают локаторыBFILE
, должны создаваться вне СУБД Oracle.
Работая с данными типа BFILE
в PL/SQL, вы работаете с локатором LOB
, который просто указывает на хранящийся на сервере файл. Поэтому в разных строках таблицы базы данных, содержащей столбец типа BFILE
, могут храниться значения, указывающие на один и тот же файл.
Локатор BFILE
состоит из псевдонима каталога и имени файла. Для получения локатора на основании этих двух параметров используется функция BFILENAME
. Псевдоним каталога представляет собой определенное в Oracle имя, соответствующее имени каталога операционной системы. Псевдонимы позволяют программам PL/SQL работать с каталогами независимо от операционной системы. Имея привилегии CREATE ANY DIRECTORY,
вы можете создать псевдоним каталога (сам каталог уже должен существовать в файловой системе) и предоставить доступ к нему следующим образом:
CREATE DIRECTORY bfile_data AS 'c:\PLSQL Book\Ch13_Misc_Datatypes\'
GRANT READ ON DIRECTORY bfile_data TO gennick;
Впрочем, создание псевдонимов каталогов и управление доступом к ним — это функции администратора базы данных, не имеющие прямого отношения к PL/SQL, поэтому я не буду подробно рассматривать их. Если вам понадобятся более подробные сведения, поговорите с администратором базы данных или ознакомьтесь с разделом документации Oracle SQL Reference Manual
, посвященным команде CREATE DIRECTORY
. Чтобы получить список каталогов, к которым у вас имеется доступ, обратитесь с запросом к представлению ALL_DIRECTORIES
.
Создание локатора BFILE
Чтобы создать локатор BFILE
, следует вызвать функцию BFILENAME
и передать ей псевдоним каталога и имя файла. В следующем примере создается локатор BFILE
для HTML- файла, который затем сохраняется в таблице waterfalls
.
DECLARE
web_page BFILE;
BEGIN
-- Удаление строки Tannery Falls, чтобы этот пример
-- мог выполняться многократно.
DELETE FROM waterfalls WHERE falls_name='Tannery Falls';
-- Вызов BFILENAME для создания локатора
web_page := BFILENAME('BFILE_DATA','Tannery_Falls.htm');
-- Сохранение локатора в таблице waterfalls
INSERT INTO waterfalls (falls_name, falls_web_page)
VALUES ('Tannery Falls',web_page);
END;
Локатор BFILE
— всего лишь комбинация псевдонима каталога и имени файла. Реальный файл и каталог даже не обязаны существовать. Иначе говоря, Oracle позволяет создавать псевдонимы для несуществующих каталогов, а BFILENAME
— локаторы для несуществующих файлов. Иногда это бывает удобно.
В имени каталога, которое вы указываете в вызовах BFILENAME
, различается регистр символов, который должен соответствовать регистру, отображаемому представлением словаря данных ALL_DIRECTORIES
. Сначала я использовал в своем примере запись bfile_data
в нижнем регистре — и был огорошен многочисленными ошибками при попытке обратиться к внешнему файлу BFILE
data
(см. следующий раздел). В большинстве случаев имя каталога при вызове BFILENAME
должно записываться в верхнем регистре.
Доступ к данным BFILE
Получив локатор BFILE
, вы можете считывать данные из внешнего файла практически так же, как из объекта BLOB
. В следующем примере из веб-страницы извлекаются первые 60 байт HTML-кода. Полученные данные, имеющие тип RAW
, преобразуются в символьную строку функцией UTL_RAW
.CAST_TO_VARCHAR2
.
DECLARE
web_page BFILE;
html RAW(60);
amount BINARY_INTEGER := 60;
offset INTEGER := 1;
BEGIN
-- Получение LOB-локатора веб-страницы
SELECT falls_web_page
INTO web_page
FROM waterfalls
WHERE falls_name='Tannery Falls';
-- Открываем локатор, читаем 60 байт и закрываем локатор
DBMS_LOB.OPEN(web_page);
DBMS_LOB.READ(web_page, amount, offset, html);
DBMS_LOB.CLOSE(web_page);
-- Чтобы результат выводился в шестнадцатеричной форме,
-- удалите символы комментария из следующей строки
--DBMS_OUTPUT.PUT_LINE(RAWTOHEX(html));
-- Преобразование данных типа RAW в читабельную символьную строку
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(html));
END;
Результат выполнения этого кода:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN
Максимальное число одновременно открытых в сеансе объектов BFILE
устанавливается параметром базы данных SESSION_MAX_OPEN_FILES
. Этот параметр определяет верхнюю границу количества файлов, которые могут быть одновременно открыты в сеансе (не только BFILE
, но и любых других файлов, включая открытые с применением программ из пакета UTL_FILE
).
Напомним, что Oracle поддерживает только чтение данных BFILE
. Тип BFILE
идеально подходит для работы с двоичными данными, созданными вне СУБД (графикой и т. д.). Например, вы можете загрузить на сервер изображения, отснятые цифровой камерой, создать локаторы BFILE
для этих файлов и работать с ними в программах PL/SQL.
Использование BFILE для загрузки столбцов LOB
Тип BFILE
не только обеспечивает доступ к двоичным файлам, созданным вне базы данных Oracle, но и является удобным средством загрузки внешних данных в столбцы LOB
. В Oracle9i
Release 1
для чтения двоичных данных из объекта BFILE
и их записи в столбец BLOB
использовалась функция DBMS_LOB.LOADFROMFILE
, а в Oracle9i Release 2
появились намного более удобные средства:
- Функция
DBMS_LOB.LOADCLOBFROMFILE
загружает в столбец типаCLOB
данные из объектаBFILE
с необходимым преобразованием символов. - Функция
DBMS_LOB.LOADBLOBFROMFILE
загружает в столбец типаBLOB
данные из объектаBFILE
. Она делает то же, чтоDBMS_LOB.LOADFROMFILE
, но ее интерфейс приближен к интерфейсу функцииDBMS_LOB.LOADCLOBFROMFILE.
Представьте, что информация о местонахождении водопада Tannery Falls
хранится во внешнем текстовом файле с именем TanneryFalls.directions
в каталоге, на который ссылается псевдоним BFILE_DATA
. Следующий пример демонстрирует использование DBMS_LOB.LOADCLOBFROMFILE
для загрузки информации в CLOB
-столбец falls_directions
таблицы waterfalls
:
/* Файл в Сети: munising_falls_03.sql */
DECLARE
Tannery_Falls_Directions BFILE
:= BFILENAME('BFILE_DATA','TanneryFalls.directions');
directions CLOB;
destination_offset INTEGER := 1;
source_offset INTEGER := 1;
language_context INTEGER := DBMS_LOB.default_lang_ctx;
warning_message INTEGER;
BEGIN
-- Удаление строки 'Tannery Falls',
-- чтобы этот пример мог выполняться многократно.
DELETE FROM waterfalls WHERE falls_name='Tannery Falls';
-- Вставка новой строки с созданием локатора LOB
-- вызовом EMPTY_CLOB()
INSERT INTO waterfalls
(falls_name,falls_directions)
VALUES ('Tannery Falls',EMPTY_CLOB());
-- Чтение локатора LOB, созданного предыдущей командой INSERT
SELECT falls_directions
INTO directions
FROM waterfalls
WHERE falls_name='Tannery Falls';
-- Открытие объекта CLOB и исходного объекта BFILE
DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(Tannery_Falls_Directions);
-- Загрузка содержимого BFILE в столбец CLOB
DBMS_LOB.LOADCLOBFROMFILE
(directions, Tannery_Falls_Directions,
DBMS_LOB.LOBMAXSIZE,
destination_offset, source_offset,
NLS_CHARSET_ID('US7ASCII'),
language_context, warning_message);
-- Проверка единственно возможного предупреждения
IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
DBMS_OUTPUT.PUT_LINE (
'Warning! Some characters couldn''t be converted.');
END IF;
-- Закрытие обоих объектов LOB
DBMS_LOB.CLOSE(directions);
DBMS_LOB.CLOSE(Tannery_Falls_Directions);
END;
Основная работа в этом фрагменте кода выполняется вызовом DBMS_LOB.LOADCLOBFROMFILE
. Эта процедура читает данные из внешнего файла, выполняет все необходимые преобразования набора символов и записывает данные в столбец CLOB
. Я использую константу DBMS_LOB.LOBMAXSIZE
для определения объема загружаемых данных. Мне нужны все данные из внешнего файла, а DBMS_LOB.LOBMAXSIZE
определяет максимальный объем, который может храниться в CLOB
.
Смещения источника и приемника начинаются с 1. Чтение должно начинаться с первого символа из BFILE
, а запись — с первого символа CLOB
. Чтобы упростить множественные последовательные вызовы LOADCLOBFROMFILE
, процедура обновляет оба смещения так, чтобы они указывали в следующую позицию за последним прочитанным символом.
Так как они являются параметрами IN OUT
, при вызове процедуры должны использоваться переменные, а не константы.
Вызов NLS_CHARSET_ID
возвращает идентификатор набора символов внешнего файла. Процедура LOADCLOBFROMFILE
преобразует загружаемые данные из этого набора в набор символов базы данных. Единственное возможное предупреждение, которое может вернуть LOADCLOBFROMFILE
, означает, что некоторые символы не могут быть преобразованы из исходного набора данных в итоговый. В программе это предупреждение проверяется в команде IF
, следующей за загрузкой.
Не путайте предупреждения с ошибками PL/SQL; загрузка все равно будет выполнена в соответствии с запросом.
Следующий пример SQL*Plus демонстрирует загрузку данных из внешнего файла с использованием LOADCLOBFROMFILE
:
SQL> SET LONG 2000
SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70
SQL> SELECT falls_directions
2 FROM waterfalls
3 WHERE falls_name='Tannery Falls';
4 /
FALLS_DIRECTIONS
----------------------------------------------------------------------
From downtown Munising, take Munising Avenue east. It will
shortly turn into H-58. Watch for Washington Street veering
off to your left. At that intersection you'll see a wooden
stairway going into the woods on your right. Go up that
stairway and follow the trail to the falls. Do not park
on H-58! You'll get a ticket. You can park on Nestor Street,
which is just uphill from the stairway.
SecureFiles и BasicFiles
Технология SecureFiles
, появившаяся в Oracle11g
, обладает рядом преимуществ по сравнению со старыми реализациями LOB
, теперь известными под общим названием BasicFiles
. Эти преимущества в основном относятся к внутренней реализации и остаются прозрачными для программистов — в коде используются те же ключевые слова, тот же синтаксис и операции. Во внутренней реализации SecureFiles
усовершенствованы многие аспекты управления LOB
, включая формат хранения данных на диске, кэширование, блокировку, отмену и алгоритмы управления дисковым пространством. Обновленная технология заметно улучшает быстродействие и позволяет устранять дублирование, сжимать и шифровать объекты LOB
посредством определения простых параметров. Кроме того, был введен новый уровень ведения журнала FILESYSTEM_LIKE_LOGGING
в дополнение к существующим режимам LOGGING
и NOLOGGING
. В новом режиме фиксируются только изменения метаданных (по аналогии с тем, как это делается в журнальных файловых системах).
По данным тестирования Oracle, повышение быстродействия от использования SecureFiles
составляет от 200 до 900%. В простом тесте с загрузкой файлов PDF
на сервер Microsoft Windows я наблюдал снижение времени загрузки от 80 до 90% от 169 секунд до 20–30 секунд (в зависимости от конфигурации и количества загрузок). В системе x86 Linux выигрыш был более скромным. Возможно, в вашей ситуации цифры будут другими, но ускорение будет наверняка!
Чтобы использовать SecureFiles
при работе с данными LOB, задайте параметру базы данных DB_SECUREFILE
значение PERMITTED
(используется по умолчанию). Кроме того, табличное пространство, в котором будут храниться данные LOB
, должно использовать технологию ASSM
(Automatic Segment Space Management
).
В Oracle Database 12c технология SecureFiles
используется по умолчанию; в Oracle Database 11g (Release 1 или Release 2)
механизм хранения определяется параметром DB_SECUREFILE
. Если задать ему значение ALWAYS
, то по умолчанию для объектов LOB
используется технология SecureFiles
; в противном случае используется BasicFiles
.
Если вы не уверены относительно базы данных, обратитесь за помощью к администратору.
Устранение дубликатов
В режиме устранения дубликатов база данных хранит только одну копию каждого объекта LOB
. Для каждого объекта генерируется хеш-код, который сравнивается с хеш-кодами других объектов LOB
в таблице или в ее разделе. Учтите, что устранение дубликатов не работает между разделами и подразделами.
Сжатие
В режиме сжатия SecureFiles
база данных сжимает данные LOB
как на диске, так и в памяти. Поддерживаются режимы MEDIUM
(среднее сжатие, используется по умолчанию) и HIGH
(высокое сжатие). Высокое сжатие занимает больше времени, но уменьшает размер данных LOB
. Простые тесты с файлами PDF
показали, что загрузка данных в режиме HIGH
занимала примерно на 25% больше времени, чем в режиме MEDIUM
.
Чтобы включить режим устранения дубликатов одновременно со сжатием, укажите соответствующие параметры в определении LOB
:
TABLE waterfalls
(
falls_name VARCHAR2 (80)
, falls_photo BLOB
, falls_directions CLOB
, falls_description NCLOB
, falls_web_page BFILE
)
LOB (falls_photo) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
LOB (falls_directions) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
LOB (falls_description) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
При включении обоих режимов сначала выполняется устранение дубликатов, а затем сжатие.
Шифрование
Режим шифрования SecureFiles
также включается в разделе LOB
команды CREATE TABLE.
Также можно выбрать алгоритм шифрования; в Oracle11g
поддерживаются алгоритмы 3DES168, AES128, AES192
(по умолчанию) и AES256
. Допускается использование любых комбинаций режимов устранения дубликатов, сжатия и шифрования:
TABLE waterfalls
(
falls_name VARCHAR2 (80)
, falls_photo BLOB
, falls_directions CLOB
, falls_description NCLOB
, falls_web_page BFILE
)
LOB (falls_photo) STORE AS SECUREFILE (COMPRESS DEDUPLICATE)
LOB (falls_directions) STORE AS SECUREFILE (ENCRYPT USING 'AES256')
LOB (falls_description) STORE AS SECUREFILE
(ENCRYPT DEDUPLICATE COMPRESS HIGH
)
Если база данных не была настроена на применение прозрачного шифрования данных (TDE Transparent Data Encryption
), шифрование LOB
потребует двух подготовительных шагов. Сначала необходимо создать бумажник (wallet
) для хранения главного ключа. Если бумажник должен храниться в стандартном каталоге ($ORACLE_BASE/ admin/$ORACLE_SID/wallet)
, его создание и открытие можно совместить в одном шаге:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "My-secret!passc0de";
Если бумажник должен храниться в нестандартном каталоге, вам придется задать этот каталог в файле SQLNET.ORA
. Так, чтобы бумажник хранился в каталоге /oracle/wallet,
включите в файл SQLNET.ORA
следующий фрагмент:
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=file)
(METHOD_DATA=(DIRECTORY=/oracle/wallet)))
Созданный бумажник необходимо открывать заново после каждого перезапуска экземпляра. Открытие и закрытие бумажника выполняется следующим образом:
ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "My-secret!passc0de";
-- Закрытие бумажника
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
Временные объекты LOB
До сих пор речь шла о долговременном хранении больших объемов неструктурированных данных за счет использования типов LOB
. Применяемые для этого типы LOB
называются постоянными. Однако многим приложениям нужны временные LOB
, используемые в качестве локальных переменных и не хранящиеся в базе данных. В этом разделе рассказывается о временных объектах LOB
и о том, как работать с ними с помощью программ встроенного DBMS_LOB
.
В Oracle8i
и последующих версиях поддерживается возможность создания, освобождения, чтения и обновления временных объектов LOB
через интерфейс OCI (Oracle Call Interface)
и вызовы DBMS_LOB
. По умолчанию временный объект LOB
существует в течение всего сеанса, в котором он создан, но многие объекты явно освобождаются и до окончания сеанса. Временные объекты типа LOB
являются удобной рабочей средой для быстрого выполнения различных операций с данными. Более высокая их производительность по сравнению с постоянными объектами LOB
достигается за счет того, что эти операции не фиксируются в журнале и для них не генерируются записи отмены. При перезаписи или обновлении объекта LOB
Oracle копирует весь объект в новый сегмент данных. Избавляясь от дополнительной нагрузки, приложение, выполняющее множество мелких операций с LOB
, может работать значительно быстрее.
Только что созданный временный объект LOB
всегда пуст; его локаторы не нужно (да и невозможно) инициализировать вызовами функций EMPTY_CLOB
и EMPTY_BLOB
. По умолчанию все временные объекты LOB
удаляются в конце сеанса, в котором они созданы. Если процесс неожиданно завершится аварийно или произойдет сбой базы данных, временные объекты LOB
будут удалены, а занимаемая ими память освободится.
Временные объекты LOB
, как и постоянные, хранятся на диске в базе данных. Термин «временные» не должен вводить вас в заблуждение. Временные объекты LOB
записываются на диск, но не связываются с определенными столбцами таблиц, располагаясь во временном табличном пространстве сеанса. Следовательно, для их использования нужно обеспечить наличие достаточного объема временного табличного пространства.
Процесс создания и освобождения временных объектов типа LOB
будет рассмотрен ниже. Затем вы узнаете, как определить, на временный или постоянный объект указывает локатор LOB
. А напоследок мы расскажем о некоторых приемах администрирования, связанных с обработкой временных объектов.
Создание временного объекта LOB
Чтобы получить экземпляр временного объекта, необходимо сначала создать его. Один из способов создания основан на вызове процедуры DBMS_LOB
.CREATETEMPORARY
. Процедура создает временный объект типа BLOB
или CLOB
и соответствующий индекс во временном пространстве. Заголовок процедуры выглядит так:
DBMS_LOB.CREATETEMPORARY (
lob_loc IN OUT NOCOPY [ BLOB | CLOB CHARACTER SET ANY_CS ],
cache IN BOOLEAN,
dur IN PLS_INTEGER := DBMS_LOB.SESSION);
Параметры DBMS_LOB
.CREATETEMPORARY
представлены в табл. 1.
Параметр | Описание |
ob_loc | Локатор LOB |
cache | Указывает, должен ли объект LOB при чтении помещаться в буферный кэш |
dur | Определяет время жизни LOB . Допустимыми значениями являются две именованные константы: DBMS_LOB.SESSION указывает, что временный объект LOB должен быть удален в конце сеанса (используется по умолчанию), а DBMS_LOB .CALL указывает, что он должен быть удален в конце текущего вызова программы, в которой он был создан |
Другой способ создания временного объекта LOB
основан на объявлении переменной LOB
в коде PL/SQL и присваивании ей значения. Например, следующий фрагмент создает временные объекты BLOB
и CLOB
:
DECLARE
temp_clob CLOB;
temp_blob BLOB;
BEGIN
-- Присваивание значения пустой переменной CLOB или BLOB
-- заставляет PL/SQL неявно создать временный объект LOB,
-- существующий на протяжении сеанса.
temp_clob :=' http://www.nps.gov/piro/';
temp_blob := HEXTORAW('7A');
END;
Я не могу однозначно порекомендовать тот или иной вариант создания временных LOB
, но вызов DBMS_LOB.CREATETEMPORARY
более четко выражает намерения программиста.
Освобождение временного объекта LOB
Процедура DBMS_LOB.FREETEMPORARY
освобождает временный объект типа BLOB
или CLOB
в текущем временном табличном пространстве. Заголовок процедуры:
PROCEDURE DBMS_LOB.FREETEMPORARY (
lob_loc IN OUT NOCOPY
[ BLOB | CLOB CHARACTER SET ANY_CS ]);
В следующем примере сначала создаются, а затем освобождаются два временных объекта LOB
:
DECLARE
temp_clob CLOB;
temp_blob BLOB;
BEGIN
-- В случае присваивания значения неинициализированной
-- переменной CLOB или BLOB PL/SQL неявно создает временный объект,
-- существующий только на время текущего сеанса.
temp_clob :='http://www.exploringthenorth.com/alger/alger.html';
temp_blob := HEXTORAW('7A');
DBMS_LOB.FREETEMPORARY(temp_clob);
DBMS_LOB.FREETEMPORARY(temp_blob);
END;
После вызова FREETEMPORARY
освобожденный локатор LOB
(lob_loc
) помечается как недействительный. Если присвоить его другому локатору с помощью обычного оператора присваивания PL/SQL, то и этот локатор будет освобожден и помечен как недействительный.
PL/SQL неявно освобождает временные объекты LOB
при выходе за пределы области действия блока.
Проверка статуса объекта LOB
Функция ISTEMPORARY
позволяет определить, является ли объект с заданным локатором временным или постоянным. Она возвращает целочисленный код: 1 для временного объекта, 0 для постоянного.
DBMS_LOB.ISTEMPORARY (
lob_loc IN [ BLOB | CLOB CHARACTER SET ANY_CS ])
RETURN INTEGER;
Обратите внимание: функция возвращает числовой код (1 или 0), а не тип данных BOOLEAN
.
Управление временными объектами LOB
Временные большие объекты обрабатываются не так, как обычные постоянные. Для них не поддерживаются транзакции, операции согласованного чтения, откаты и т. д. Такое сокращение функциональности имеет ряд следствий:
- Если при обработке временного объекта
LOB
произойдет ошибка, этот объект приходится освобождать и начинать все заново. - Одному временному объекту
LOB
не следует присваивать несколько локаторов. Отсутствие поддержки операций согласованного чтения и отката при использовании нескольких локаторов может привести к значительному снижению производительности. - Если пользователь модифицирует временный объект
LOB
, на который указывает еще один локатор, создается глубокая копия данного объекта. После этого локаторы будут ссылаться на разные объекты. Директива компилятораNOCOPY
запрещает глубокое копирование при передаче локаторовLOB
в аргументах. - Чтобы преобразовать временный объект в постоянный, нужно вызвать программу
DBMS_LOB
.COPY
и скопировать временный объектLOB
в постоянный. Временные локаторыLOB
действуют только в рамках сеанса. Локатор нельзя передать из одного сеанса в другой. Если объектLOB
должен использоваться в разных сеансах, сделайте его постоянным.
В Oracle9i Database
появилось представление с именем V$TEMPORARY_LOBS
, которое содержит информацию о количестве существуюих кэшированных и некэшированных объектов LOB
для сеанса. Администратор базы данных может объединить информацию V$TEMPORARY_LOBS
с представлением словаря данных DBA_SEGMENTS
, чтобы узнать, сколько дискового пространства сеанс использует для хранения временных LOB
.
Встроенные операции LOB
С первых дней появления функциональности LOB
в Oracle многочисленные пользователи, программисты и разработчики запросов желали использовать LOB
как очень большие разновидности обычных скалярных переменных. В частности, пользователи хотели работать с CLOB
как с огромными строками, передавать их функциям SQL, использовать в условиях WHERE
команд SQL и т. д. К их огорчению, объекты CLOB
изначально не могли использоваться вместо VARCHAR2
. Например, в Oracle8 Database и Oracle8i Database к столбцу CLOB
нельзя было применить символьную функцию:
SELECT SUBSTR(falls_directions,1,60)
FROM waterfalls
Начиная с Oracle9i Database
, объекты CLOB
могут заменять VARCHAR2
во многих ситуациях:
- Объекты
CLOB
могут передаваться многимVARCHAR2
-функциям PL/SQL и SQL — эти функции существуют в перегруженных версиях с параметрамиVARCHAR2
иCLOB
. - В PL/SQL, но не в SQL, с переменными
LOB
могут использоваться различные операторы отношений, такие как «меньше» (<), «больше» (>) и «равно» (=). - Значения
CLOB
можно присваивать переменнымVARCHAR2
, и наоборот. Также можно осуществлять выборку значенийCLOB
в переменныеVARCHAR2
, и наоборот. Такая возможность существует благодаря тому, что PL/SQL теперь выполняет неявные преобразования между типамиCLOB
иVARCHAR2
.
Семантика SQL
Возможности, упомянутые в предыдущем разделе, Oracle называет «поддержкой семантики SQL для LOB
». С точки зрения разработчика PL/SQL это означает, что с LOB
можно работать на уровне встроенных операторов (вместо отдельного пакета).
Следующий пример демонстрирует некоторые возможности семантики SQL:
DECLARE
name CLOB;
name_upper CLOB;
directions CLOB;
blank_space VARCHAR2(1) := ' ';
BEGIN
-- чтение VARCHAR2 в CLOB и применение функции к CLOB
SELECT falls_name, SUBSTR(falls_directions,1,500)
INTO name, directions
FROM waterfalls
WHERE falls_name = 'Munising Falls';
-- Преобразование CLOB к верхнему регистру
name_upper := UPPER(name);
-- Сравнение двух CLOB
IF name = name_upper THEN
DBMS_OUTPUT.PUT_LINE('We did not need to uppercase the name.');
END IF;
-- Конкатенация CLOB со строками VARCHAR2
IF INSTR(directions,'Mackinac Bridge') <> 0 THEN
DBMS_OUTPUT.PUT_LINE('To get to ' || name_upper || blank_space
|| 'you must cross the Mackinac Bridge.');
END IF;
END;
Результат:
To get to MUNISING FALLS you must cross the Mackinac Bridge.
Маленький фрагмент кода в этом примере содержит несколько интересных моментов:
- Столбец
falls_name
имеет типVARCHAR2
, однако он читается в переменнуюCLOB
, что демонстрирует неявное преобразование между типамиVARCHAR2
иCLOB
. - Функция
SUBSTR
ограничивает выборку первыми 500 символами описания, а функцияUPPER
преобразует название водопада к верхнему регистру. Тем самым демонстрируется применение функций SQL и PL/SQL кLOB
. - Команда
IF
, сравнивающая name сname_upper
, выглядит немного неестественно, но она демонстрирует возможность применения операторов отношения кLOB
. - Название, приведенное к верхнему регистру —
CLOB
, — объединяется со строковыми константами и одной строкойVARCHAR2
(blank_space
). Тем самым демонстрируется возможность конкатенацииCLOB
.
При использовании этой функциональности приходится учитывать многочисленные ограничения и скрытые ловушки. Например, не каждая функция, получающая значение VARCHAR2
, примет вместо него CLOB
; есть и исключения. В частности, функции регулярных выражений работают с семантикой SQL, а агрегатные функции — нет; не все операторы отношений поддерживаются для LOB
и т. д. Все эти ограничения подробно описываются в разделе «SQL Semantics and LOB
s» главы 10 руководства SecureFiles and Large Objects Developer’s Guide
для Oracle Database 11g и 12c. Для Oracle Database 10g обращайтесь к главе 9, «SQL Semantics and LOB
», руководства Application Developers Guide — Large Objects
. Если вы собираетесь использовать семантику SQL, я настоятельно рекомендую ознакомиться с соответствующим разделом руководства для вашей базы данных.
Семантика SQL для LOB
действует только для внутренних типов LOB: CLOB, BLOB
и NCLOB
. Поддержка семантики SQL не относится к BFILE
.
Семантика SQL может создавать временные объекты LOB
При применении семантики SQL к LOB
необходимо учитывать один важный аспект: она часто приводит к созданию временного объекта LOB
. Подумайте, как функция UPPER
применяется к CLOB
:
DECLARE
directions CLOB;
BEGIN
SELECT UPPER(falls_directions)
INTO directions
FROM waterfalls
WHERE falls_name = 'Munising Falls';
END;
Так как объем данных CLOB
очень велик, эти объекты хранятся на диске. База данных не может преобразовать к верхнему регистру читаемый объект CLOB
, потому что это означает изменение его значения на диске — по сути это означает изменение читаемого значения. Также база данных не может внести изменения в копию CLOB
в памяти, потому что значение может не поместиться в памяти, а в результате выборки программа получает только локатор, который указывает на значение на диске. Единственное, что может сделать база данных, — создать временный объект CLOB
во временном пространстве. Функция UPPER
копирует данные из исходного объекта CLOB
во временный объект CLOB
, преобразуя символы к верхнему регистру в процессе копирования. Затем команда SELECT
возвращает локатор LOB
, указывающий на временный, а не на исходный объект CLOB
. У этого факта есть два очень важных следствия:
- Локатор, возвращаемый функцией или выражением, не может использоваться для обновления исходного объекта
LOB
. Переменная directions в моем примере не может использоваться для обновления постоянного объектаLOB
, хранящегося в базе данных, потому что она в действительности указывает на временный объектLOB
, возвращаемый функциейUPPER
. - Создание временного объекта
LOB
требует расхода дискового пространства и вычислительных ресурсов. Эта тема более подробно рассматривается в разделе «Влияние семантики SQL на быстродействие».
Чтобы получить описание местонахождения водопада в верхнем регистре, сохранив возможность обновления исходного описания, необходимо получить два локатора LOB
:
DECLARE
directions_upper CLOB;
directions_persistent CLOB;
BEGIN
SELECT UPPER(falls_directions), falls_directions
INTO directions_upper, directions_persistent
FROM waterfalls
WHERE falls_name = 'Munising Falls';
END;
Теперь я могу обратиться к описанию в верхнем регистре через локатор в directions_upper
и изменить исходное описание через локатор в directions_persistent
. Выборка лишнего локатора в данном случае не отражается на быстродействии приложения — на него влияет преобразование текста к верхнему регистру и его сохранение во временном объекте CLOB
. Локатор в directions_persistent
просто читается из таблицы базы данных.
В общем случае любая символьно-строковая функция, которая обычно получает VARCHAR2
и возвращает VARCHAR2
, при передаче CLOB
вернет временный объект CLOB
. Аналогичным образом выражения, возвращающие CLOB
, почти наверняка будут возвращать временные объекты CLOB
. Временные объекты CLOB
и BLOB
не могут использоваться для обновления данных LOB
, использованных в выражении или функции.
Влияние семантики SQL на быстродействие
Используя новую семантику SQL при работе с LOB
, стоит учесть ее последствия для быстродействия. Помните, что данные LOB
могут занимать до 128 терабайт (4 гигабайта до Oracle Database 10g). Соответственно, при неосмотрительном использовании LOB
как любого другого типа переменной или столбца могут возникнуть серьезные проблемы. Взгляните на следующий запрос, который пытается найти все водопады, путь к которым проходит через мост Макинак:
SELECT falls_name
FROM waterfalls
WHERE INSTR(UPPER(falls_directions),'MACKINAC BRIDGE') <> 0;
Представьте, как должна действовать база данных Oracle при обработке этого запроса. Для каждой строки в таблице waterfalls
она должна взять содержимое столбца falls_directions
, преобразовать его к верхнему регистру и поместить результаты во временный объект CLOB
(находящийся во временном табличном пространстве). Затем ко временному объекту LOB
применяется функция INSTR
, которая ищет строку «MACKINAC BRIDGE
». В моих примерах описания были относительно короткими. Но представьте, что falls_directions
содержит действительно большие данные LOB
, а средний размер столбца составляет 1 Гбайт. Естественно, выделение пространства для всех временных объектов LOB
при преобразовании описаний к верхнему регистру приведет к быстрому расходованию временного табличного пространства.
А теперь представьте, сколько времени потребуется для создания копии каждого объекта CLOB
для его преобразования к верхнему регистру, для выделения и освобождения пространства временных объектов CLOB
во временном пространстве и для посимвольного поиска данных в CLOB
средним объемом в 1 Гбайт. Конечно, такие запросы навлекут на вас гнев администратора базы данных.
Oracle Text и семантика SQL
Если вам понадобится выполнить запросы с эффективным поиском в CLOB
, возможно, вам поможет Oracle Text. Допустим, когда-нибудь вам потребуется написать запрос следующего вида:
SELECT falls_name
FROM waterfalls
WHERE INSTR(UPPER(falls_directions), 'MACKINAC BRIDGE') <> 0;
Если falls_directions
является столбцом CLOB
, эффективность такого запроса оставляет желать лучшего. Однако при использовании Oracle Text вы можете определить для этого столбца индекс Oracle Text, игнорирующий регистр символов, после чего воспользоваться предикатом CONTAINS
для эффективной обработки запроса:
SELECT falls_name
FROM waterfalls
WHERE
CONTAINS(falls_directions,'mackinac bridge') > 0;
За дополнительной информацией о CONTAINS
и индексах Oracle Text, игнорирующих регистр символов, обращайтесь к документации Oracle Text Application Developer’s Guide.
Из-за возможных негативных последствий от применения семантики SQL к LOB
в документации Oracle рекомендуется ограничить ее данными LOB
, размер которых не превышает 100 Кбайт. Я не готов предложить конкретные рекомендации по размеру; рассмотрите каждую ситуацию в контексте своих обстоятельств. Всегда учитывайте возможные последствия от применения семантики SQL к LOB
; возможно, вам стоит провести тестирование для оценки этих последствий, чтобы принять разумное решение в вашей конкретной ситуации.
Функции преобразования объектов LOB
Oracle предоставляет в распоряжение программиста несколько функций преобразования больших объектов. Перечень этих функций приведен в табл. 2.
Функция TO_LOB
предназначена для одноразового перемещения данных LONG
и LONG RAW
в столбцы типа CLOB
и BLOB
, поскольку типы LONG
и LONG RAW
теперь считаются устаревшими. Функции TO_CLOB
и TO_NCLOB
предоставляют удобные средства преобразования символьных данных больших объектов и наборов символов базы данных и национальных языков.
Функция | Описание |
TO_CLOB(символьные_данные) | Преобразует символьные данные (типы VARCHAR2 , NVARCHAR2, CHAR, NCHAR, CLOB и NCLOB ) в данные типа CLOB . При необходимости (для входных данных типа NVARCHAR2 ) данные преобразуются из национального набора в набор символов базы данных |
TO_BLOB(данные_типа_raw) | Аналог функции TO_CLOB ; преобразует данные RAW или LONG RAW к типу BLOB |
TO_NCLOB(символьные_данные) | Аналог функции TO_CLOB ; результат имеет тип NCLOB и представлен символами национального набора |
TO_LOB(данные_long) | Принимает значение типа LONG или LONG RAW и преобразует эти данные в CLOB или BLOB соответственно. Функция TO_LOB может быть вызвана только из списка выборки подзапроса в команде INSERT...SELECT...FROM. |
TO_RAW | Принимает значение типа BLOB и возвращает его как значение типа RAW |