Опыт использования таблиц с LOB-столбцами

Тихомиров Владимир Вадимович,
Директор по информационным технологиям ОСАО “Ингосстрах”

Михеичев Валерий Вадимович,
Эксперт Oracle, ОСАО “Ингосстрах”

Существует несколько типов LOB объектов: BLOB (двоичный большой объект), CLOB (символьный большой объект), NCLOB (национальный символьный большой объект) и BFILE (внешний двоичный файл). LOB объекты делятся на внутренние и внешние.

Внутренние большие объекты Internal LOB – хранятся в базах данных (БД), к ним относятся BLOB, CLOB и NCLOB. Внутренние большие объекты могут быть постоянными или временными:

• постоянные LOB создаются как столбцы таблицы БД командой CREATE таблицы, при этом данные LOB могут храниться как в самой таблице, так и вне таблицы;
• временные Temporary LOB создается для использования только в пределах конкретного приложения. Для создания временного LOB используется процедура DBMS_LOB.CREATETEMPORARY.

Внешние большие объекты External LOB – вид данных, который хранится в файлах операционной системы, вне базы данных, а в базе данных на них хранятся ссылки (локаторы). Внешние LOB используют тип данных – BFILE.

LOB объект состоит из двух частей:

• локатор-указатель, который специфицирует местонахождение контента, т.е. собственно данных LOB объекта;
• контент – набор двоичных или символьных байтов, составляющих LOB.

Инструментом работы с LOB выступает пакет DBMS_LOB. Он предоставляет методы манипулирования внутренними и внешними LOBами.

В данной статье рассматриваются только постоянные внутренние LOB объекты.

Сегменты LOB объектов Oracle

При создании и заполнении данными таких объектов Oracle как LOB объект, таблица, индекс и др. для каждого объекта создается структура, называемая сегментом. При этом имеется особенность при создании сегментов: для каждой не секционированной таблицы создается только один сегмент, для одного не секционированного индекса создается только один сегмент, для каждой секции секционированной таблицы и для каждой секции индекса создается свой сегмент. Однако для каждого LOB столбца таблицы создается не один, а два сегмента. Один сегмент - это LOB-сегмент для хранения данных и второй – это LOB индексный сегмент. LOB индексный сегмент создается автоматически при создании LOB-сегмента и служит для навигации по LOB-сегменту.

Таким образом, если в таблице было указано N столбцов типа LOB, то получим 2*N LOB-сегментов.

LOB сегменты можно увидеть из представления DBA_LOBS запросом:

Select  owner, table_name, column_name, segment_name, index_name, tablespace_name
From DBA_LOBS  Where Owner=’ИМЯ СХЕМЫ’ and Table_name = ‘ИМЯ ТАБЛИЦЫ’;

Рассмотрим порядок работы с таблицами, имеющими LOB столбцы, на примере таблицы AIS.SERVICEMSGXML, функционирующей в нашей системе. В таблице имеются два LOB столбца: XMLOUT и XMLIN - оба типа CLOB.

Команда создания таблицы имеет вид:

CREATE TABLE AIS.SERVICEMSGXML
(  ISN           NUMBER,
  XMLOUT        CLOB,
  BMLIN            CLOB
  )
TABLESPACE ADATA
LOB (XMLOUT) STORE AS 
      ( TABLESPACE  LOBTBS 
        ENABLE      STORAGE IN ROW
       CHUNK     16384)
LOB (XMLIN) STORE AS 
      ( TABLESPACE  LOBTBS2 
        ENABLE      STORAGE IN ROW
        CHUNK       8192    );

LOB сегменты этой таблицы можно увидеть из представления DBA_LOBS указанным выше запросом:

Select  * From DBA_LOBS  Where owner=’AIS’ and table_name = ‘SERVICEMSGXML’;

В результате получаем:

OWNER	TABLE_NAME	COLUMN_NAME	SEGMENT_NAME			INDEX_NAME			TABLESPACE_NAME
AIS	SERVICEMSGXML	XMLOUT		SYS_LOB0001677384C00014$$	SYS_IL0001677384C00014$$	LOBTBS
AIS	SERVICEMSGXML	XMLIN		SYS_LOB0001677384C00015$$	SYS_IL0001677384C00015$$	LOBTBS2

Мы видим, что в именах сегментов имеются символы SYS_LOB для LOB-сегментов и SYS_IL для индексных сегментов.

Используя представление Oracle DBA_SEGMENTS (при наличии данных в LOB), можно получить размер в байтах, занимаемых LOB-сегментами и LOB-индексными сегментами. Например, для LOB столбца XMLOUT число байтов, занимаемых сегментом, определяется запросом, указанным ниже. При этом в запросе в поле segment_name подставляется имя сегмента, полученного из представления DBA_LOBS.

Select owner, segment_name, bytes, tablespace_name From DBA_SEGMENTS Where segment_name = ‘SYS_LOB0001677384C00014$$';

В результате работы запроса получаем:

OWNER	SEGMENT_NAME			BYTES		TABLESPACE_NAME
AIS	SYS_LOB0001677384C00014$$	6969884672	LOBTBS

Для работы с LOB удобным является запрос, в котором используются оба указанных выше представления: DBA_SEGMENTS и DBA_LOBS. Данный запрос покажет (при наличии данных в LOB) не только LOB сегменты всей таблицы и ее индексов, но и размеры в байтах, которые они занимают.

 
Select s.owner, d.table_name, d.column_name, s.segment_name, s.segment_type, s. bytes,
s.tablespace_name From DBA_SEGMENTS s, DBA_LOBS d
Where s.segment_name in (d.segment_name,d.index_name) and s.owner=d.owner
and d.owner='AIS' and d.table_name = 'SERVICEMSGXML' order by 3;

OWNER	TABLE_NAME	COLUMN_NAME	SEGMENT_NAME			SEGMENT_TYPE	BYTES
AIS	SERVICEMSGXML	XMLIN		SYS_IL0001677384C00015$$	LOBINDEX	65536
AIS	SERVICEMSGXML	XMLIN		SYS_LOB0001677384C00015$$	LOBSEGMENT	131072
AIS	SERVICEMSGXML	XMLOUT		SYS_LOB0001677384C00014$$	LOBSEGMENT	6969884672
AIS	SERVICEMSGXML	XMLOUT		SYS_IL0001677384C00014$$	LOBINDEX	6291456

Иногда важно посмотреть объем, занимаемый самой таблицей без LOB объектов. Это можно увидеть (при наличии данных в таблице) запросом:

Select s.owner, s.segment_name, s.segment_type, s.bytes, s.tablespace_name
From DBA_SEGMENTS s Where owner='AIS' and  segment_name = 'SERVICEMSGXML';

OWNER	SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME	BYTES
AIS	SERVICEMSGXML	TABLE	ADATA	8388608

Как видно, сама таблица составляет порядка 8 MB, в то время как один из LOB занимает 6900 MB. В силу чего таблица была размещена в табличном пространство ADATA среднего размера, в то время как LOB большого объема был размещен в другом существенно большем табличном пространстве LOBTBS. О табличных пространствах LOB изложено ниже.

Задание табличного пространства для LOB столбцов таблицы

По умолчанию LOB-сегмент со своим LOB индексным сегментом создается в том же табличном пространстве, что и таблица, в которой находятся LOB столбец. При этом весьма важный момент: LOB-сегмент и LOB-индекс всегда находятся в одном и том же табличном пространстве.

Однако порой целесообразно помещать LOB сегмент в табличное пространство, отличное от пространства таблицы. При этом сами LOB столбцы так же могут размещаться в различных табличных пространствах.

Для размещения LOB в другое табличное пространство, отличное от пространства таблицы, используется структура STORE (начиная с Oracle 11g можно использовать структуру STORE AS SECUREFILE) в команде CREATE таблицы. При этом фразу STORE можно указать только при создании таблицы, т.е. STORE нельзя вставить в процессе модификации таблицы.

Замечание. При создании таблицы по команде Create table с LOB столбцами в ней автоматически для каждого LOB столбца таблицы создается структура STORE. Например, для столбца XMLOUT типа LOB по умолчанию создалась бы структура STORE вида:

LOB (XMLOUT) STORE AS (
TABLESPACE  ADATA
ENABLE      STORAGE IN ROW
CHUNK       8192
RETENTION
NOCACHE
LOGGING).

В ней по умолчанию табличным пространство LOB-а становиться табличное пространство таблицы ADATA и автоматически создаются несколько параметров.

Если есть необходимость управлять табличным пространством или параметрами STORE, надо явно описать конструкцию STORE при создании таблицы (что и было сделано при создании таблицы AIS.SERVICEMSGXML по каждому LOB столбцу). При этом, если используется конструкция STORE AS SECUREFILE, то ее необходимо явно прописывать в команде Create table.

Основными атрибутами конструкции STORE являются:

• Фраза TABLESPACE, указывающая в каком табличном пространстве создается LOB сегмент (соответственно и его LOB-индексный сегмент).
  


• Фраза ENABLE. По умолчанию создается ENABLE STORAGE IN ROW - это означает, что LOB размером до 4000 байт будут храниться в самой таблице (сегменте таблицы), а LOB-ы, который больше 4000 байт, будут храниться в LOB сегменте вне таблицы. При DISABLE STORAGE IN ROW -все значения LOB попадают в LOB сегменты, т.е. вне таблицы.
  При этом предпочтительнее ставить ENABLE STORAGE IN ROW, если в основной массе случаев размер LOB меньше 4000 байт, т.е. LOB-ы умещаются в таблице. В отсутствии кэширования LOB, каждое обращение к LOB (чтение или запись) задействует физическую операцию ввода-вывода. Таким образом, хранение LOB-ов в таблице позволяет существенно сократить операции физического ввода-вывода на извлечение, запись и модификацию LOB-ов. ENABLE позволяет так же уменьшить число логических операциях ввода-вывода, обусловленных обращением к LOB-индексу.
  Замечание. Для принятия решения, какой метод принять ENABLE STORAGE IN ROW или DISABLE может использоваться функция GETLENGTH пакета DBMS_LOB.
  Данная функция определяет размер LOB в байтах для BLOB и BFILE или длину LOB в символах для CLOB. При этом перевод числа символов в число байтов зависит от кодировки, так для ASCII и ряда других кодировок один символ = 1 байт. Таким образом, функция GETLENGTH практически в этом случае даст тоже размер в байтах.
  При этом ценность функции в том, что она позволяет показать не общую сумму байт, занимаемую LOB, а число байт, занимаемую LOB по строкам таблицы. В силу чего можно оценит, какой процент строк занимает менее 4000 байт и принять решение по поводу фразы ENABLE.
  Например, для столбца XMLIN типа CLOB число символов, занимаемое соответствующими строками таблицы SERVICEMSGXML, определяется запросом
  

  SELECT DBMS_LOB.GETLENGTH(XMLIN)  FROM AIS.SERVICEMSGXML ORDER BY 1 DESC;

  
  Вычислить процент строк, в которых LOB типа BLOB составляют менее 4000 байт можно по запросу
  

  SELECT ROUND(SUM(S1)/SUM(S2),3)*100 FROM ( SELECT COUNT(*) s1, 0 s2 FROM 
  AIS.SERVICEMSGXML WHERE DBMS_LOB.GETLENGTH(XMLIN)<=4000
  UNION ALL
  SELECT 0,COUNT(*) s2 FROM AIS.SERVICEMSGXML) ;

  
  (для типа CLOB этот запрос тоже может использоваться в случае кодировки, в которой 1 символ=1 байту, либо с учетом поправочного коэффициента при другой кодировке).
  


• Фраза CHUNK определяет размер порции данных, размещенных в LOB. В отличие от таблицы, где минимальной единицей размещения данных является блок, в LOB данные хранятся в виде порций - логически непрерывного набора блоков, являющийся минимальным единицей размещения LOB. Таким образом, каждое хранимое значение LOB потребляет минимум один CHUNK. Размер CHUNK задается кратным размеру блока данных db_block_size базы данных и определяется из запроса Select value from v$parameter where name ='db_block_size'; В базе данных, в которой мы работаем, размер блока данных=8192.
  Обычно CHUNK задается равным db_block_size, например 8192, но может быть равным 16384, 24576, 32768 и т.д. В приведенной выше таблице один из LOB имеет значение 16384.
  При этом значение CHUNK больше, чем 8192 должно быть обоснованным, поскольку с одной стороны это может повысить производительность работы с LOB, а с другой стороны может привести к не эффективному расходованию пространства в силу только частичного заполнения пространства порции CHUNK. Например, если таблица содержит LOB столбец, строки которого менее 8192 байт, а выбрали CHUNK равным 32768 байт, то 32768-8192=24576 байт растрачивается впустую.
  При выборе CHUNK используется следующий подход. Сначала определяется процент строк, которые умещаются в 8192. Если этот процент составляет большинство, то CHUNK принимаем равным 8192, если нет, то проделываем ту же процедуру с 16384 и т.д. Ниже предлагается запрос, позволяющий оценить распределение размеров LOB строк в байтах по таблице.

  select DBMS_LOB.GETLENGTH(XMLOUT) bytes, count(1) distincts from 
  AIS.SERVICEMSGXML m group by DBMS_LOB.GETLENGTH(XMLOUT) order by 2 desc;

• Фраза NOCACHE. По умолчанию принимает значение NOCACHE, т.е. запрещает кэшировать LOB. Поскольку LOB, как правило, велики, то в большинстве случаев целесообразно отключать кеширование LOB. CACHE - включает кеширование LOB. При этом кеширование относится только к LOB-объектам, не затрагивая саму таблицу.
  


• Фраза LOGGING. По умолчанию имеет значение LOGGING, означающее, что изменения данных в LOB-объектах записываются в redo-журнал для восстановления данных LOB в случае сбоя базы. Однако, поскольку данные в LOB-объектах обычно велики, то может быть включен режим NOLOGGING, позволяет отключить формирование redo-информации.

В Oracle 11g структура STORE получила дальнейшее развитие, так появилась возможность сжимать данные LOB-ов и исключить их дублирование. Для этого вместо конструкции STORE пишется конструкция STORE AS SECUREFILE. В ней дополнительно к существующим атрибутам добавились новые атрибуты, среди них:

• Фраза COMPRESS. Задает режим сжатия: HIGH –высокое сжатие , MEDIUM-среднее (по умолчанию) и LOW-низкое. Сжатие происходит внутри LOB, каждый LOB сжимается независимо от других. При этом сжатие LOB не зависит от сжатия таблиц. Так если задали режим сжатия таблицы, то это не приведет автоматически к сжатию ее LOB и наоборот. При выборе режима сжатия надо учитывать, что оно требует работы процессора, так в режиме LOW хотя и получаем минимальное сжатие, однако потребуется намного меньше потребление процессорных ресурсов, кроме того обеспечивается более высокая скорость процесса.
  Для проверки эффективности сжатия можно использовать указанный выше пакет DBMS_LOB.GETLENGTH. Для этого оценивается размер LOB до сжатия и после сжатия (за какой-то период времени t). Например, для LOB XMLIN может использоваться запрос

  SELECT SUM(DBMS_LOB.GETLENGTH(XMLIN))  FROM AIS.SERVICEMSGXML T
  WHERE T.UPDATED >SYSDATE-1/24;

  
  Перед этим можно убрать режим сжатия LOB через NOCOMPRESS по команде

  ALTER TABLE  AIS.SERVICEMSGXML MODIFY   LOB(XMLIN) (NOCOMPRESS);

  
  Выполнить указанный выше запрос через время t, за которое будут проведены вставки данных в таблицу, а затем ввести режим сжатия в таблицы

  ALTER TABLE  AIS.SERVICEMSGXML MODIFY   LOB(XMLIN) (COMPRESS HIGH);

  
  Посчитать объем (в байтах) данных, введенных в таблицу за тоже t время, после чего сравнить результаты.
  


• Фраза DEDUPLICATE запускает режим дедубликации. В этом режиме повторяющиеся значения в LOB заменяются указателями, что экономит табличное пространство.

Вывод. При создании STORE cледует оценить, что эффективнее: хранить LOB в таблице или вынести их из таблицы (режим ENABLE или DISABLE), обосновать целесообразность кэширования LOB и определить более эффективный размер CHUNK.

Модификация атрибутов конструкции STORE

Ряд атрибутов STORE могут быть добавлены или модифицированы в любой момент после создания таблицы командой ALTER TABLE (не модифицируются такие атрибуты как CHUNK и ENABLE/DISABLE STORAGE IN ROW).

Например, для столбца XMLIN таблицы SERVICEMSGXML модификация атрибутов NOCACHE на CACHE осуществляется так же по команде ALTER

ALTER TABLE  AIS.SERVICEMSGXML MODIFY     LOB (XMLIN)  (CACHE);

Для режима STORE AS SECUREFILE можно модифицировать или добавлять новые атрибуты. Например, добавить DEDUPLICATE и модифицировать COMPRESS с LOW на HIGH по команде:

ALTER TABLE  AIS.SERVICEMSGXML MODIFY   LOB (XMLIN)  (DEDUPLICATE);
ALTER TABLE  AIS.SERVICEMSGXML MODIFY   LOB(XMLIN) (COMPRESS HIGH);

Особенности очистки табличного пространства, занимаемого LOB объектами

В отличие от обычных таблиц удаление данных из таблицы с LOB не освобождает табличное пространство, занимаемое LOB. Для освобождения табличного пространства используется команда SHRINK либо TRUNCATE таблицы или (что более эффективно) TRUNCATE секции для секционированных таблиц.

Сжатие пространства для не секционированной таблицы осуществляется по команде

ALTER TABLE SERVICEMSGXML  MODIFY LOB (XMLIN) (SHRINK SPACE CASCADE);

Для секционированной таблицы команды с shrink и truncate имеют вид:

ALTER TABLE имя таблицы   MODIFY PARTITION имя партиции
LOB (имя LOB столбца) (SHRINK SPACE);
ALTER TABLE имя таблицы   TRUNCATE PARTITION имя партиции
UPDATE GLOBAL INDEXES;

Фраза UPDATE GLOBAL INDEXES в TRUNCATE обеспечивает исправность глобальных индексов после очистки секции по Truncate.

Выводы

1. Для каждого LOB столбца таблицы автоматически создают два LOB сегмента.
2. По умолчанию LOB сегменты попадают в то же табличное пространство, что и таблица. Если LOB столбцы требуется размещать в другое табличное пространство, то используется конструкция STORE или STORE AS SECUREFILE.
   Дальнейшим развитием конструкции STORE в Oracle 11g является новая конструкция STORE AS SECUREFILE , позволяющая сжимать и уменьшать число дублирующей информации в LOB объектах.
3. Возможна модификация ряда атрибутов конструкции STORE (STORE AS SECUREFILE).
4. Для сжатия табличного пространства LOB объектов используюсь shrink или truncate.