Архивирование данных с использованием XML-формата

Страховая компания Acme Insurance Company готовится к архивированию на ленту данных, срок хранения которых равен трем годам и больше, и удалению их из промышленной базы данных. ИТ-архитекторы компании предложили стратегию архивирования, в которой используются переносимые табличные пространства, а также аккуратное секционирование промышленных таблиц по датам выполнения транзакций.

В соответствии с исходным архитектурным планом секции с устаревшими данными преобразуются в отдельные таблицы с помощью соответствующих операторов механизма секционирования (partition exchange), затем табличные пространства, содержащие эти таблицы, могут быть перенесены на ленту и удалены из главной базы данных. Если удаленные данные нужно восстановить, перенесенные табличные пространства копируются с ленты и вновь включаются в главную базу данных. В переносимых табличных пространствах и преобразованных секциях данные не модифицируются, поэтому не генерируется никакая UNDO- и REDO-информация, что позволяет существенно ускорить перемещение данных по сравнению с использованием традиционных подходов, таких как использование операторов INSERT и DELETE. И если в архивных данных нужно искать какую-то информацию, то это также просто, как выполнение запросов к отдельным таблицам, которые были созданы во время преобразования секций.

План можно было бы считать совершенным, но была одна проблема. ИТ-архитекторы в других проектах планируют постепенное изменение структуры промышленных таблиц. Будут добавляться и удаляться столбцы, изменяться типы данных, модифицироваться ограничения целостности и т.д. После изменения структуры таблиц перенесенные табличные пространства уже нельзя будет снова включить в главную базу данных - структуры таблиц не совпадают!

Для решения этой проблемы был отозван из отпуска Джон, главный администратор базы данных компании.

XML-формат архивных данных

Джон закатил рукава и сформулировал пять главных требований к процессу архивирования:

1. Данные должны архивироваться по мере их старения.
2. Заархивированные данные должны удаляться из главной базы данных без большого влияния на производительность.
3. Восстановление архивных данных должно быть быстрым и простым.
4. Решение должно позволять изменение структур таблиц.
5. Должна быть обеспечена возможность поиска в архивных данных без включения их в главную базу данных.

Джон решил использовать для хранения архивных данных XML-формат.

Администраторы базы данных и программисты компании Acme отреагировали на предложение Джона, высказав соображения, что их приложения разработаны с учетом использования реляционной модели и обычных таблиц, что эти приложения (и таблицы) не могут обрабатывать XML-данные, что для досконального анализа влияния этих изменений нет времени.

Джон ответил, что программы будут видеть данные как если бы они были реляционными, хотя на самом деле они будут храниться во внешней памяти в XML-формате.

Архивная таблица

Джон предложил всем заинтересованным программистам и администраторам базы данных объяснить свое решение об архивировании в XML-формате на примере типичной в их базе данных таблицы сделок с клиентами (транзакций) TRANS, начиная с момента ее создания и заполнения (см. листинг 1). Эта таблица секционирована по столбцу TRANS_DATE (дата транзакции), имея в виду, что через три года самая старая секция может быть заархивирована и полностью удалена из таблицы (drop partition). Такое удаление секции фактически не оказывает никакого влияния на производительность сервера базы данных и генерирует очень мало REDO- и UNDO-информации; это самый быстрый способ очистки данных по сравнению с такими методами, как построчное удаление данных и усечение таблицы (truncation).

create table trans
(
	trans_date		date,
	trans_id			number(10),
	trans_type		varchar2(1),
	trans_amount	number(12,2)
)
partition by range (trans_date)
(
	partition Jan2003 values less than (to_date('01/02/2003', 'dd/mm/yyyy')),
	partition Feb2003 values less than (to_date('01/03/2003', 'dd/mm/yyyy')),
.
.
.
);

begin
	for ctr in 1..100 loop
		insert into trans values
		(
			sysdate - dbms_random.value(1,300),
			ctr,
			decode (round(dbms_random.value(1,2)),1,'C','D'),
			dbms_random.value(1,10000)
		);
	end loop;
end;
/

Однако, поскольку структура промышленной таблицы будет изменяться, в базе данных компании Acme нужно, пояснил Джон, создать еще одну таблицу - специально для целей архивирования. Столбцы этой таблицы ARCH_TRANS не будут совпадать со столбцами промышленной таблицы; большинство значений которых будет размещаться в архивной таблице в одном столбце TRANS_DETAILS, тип данных которого - XMLTYPE:

create table arch_trans
(
   arch_date date,
   trans_date date,
   trans_details xmltype
)
partition by range (trans_date)
(
   partition Jan2003 values less than (to_date('01/02/2003', 'dd/mm/yyyy')),
   partition Feb2003 values less than (to_date('01/03/2003', 'dd/mm/yyyy')),
.
.
.
)
/

Джон отметил два важных момента:

• архивная таблица ARCH_TRANS секционируется по столбцу TRANS_DATE таким же способом как и таблица TRANS;
• кроме столбца TRANS_DATE в таблице ARCH_TRANS нет никаких других столбцов исходной таблицы. Вместо них появился новый столбец TRANS_DETAILS, в котором в XML-формате содержатся значения других столбцов промышленной таблицы.

Архивирование

Для преобразования данных из реляционного формата в XML-формат Джон использует две SQL-функции: XMLFOREST и XMLELEMENT. Функция XMLFOREST преобразует реляционные данные в XML-формат. Значения преобразуемых столбцов заключаются в теги, имена которых совпадают с именами столбцов. Для демонстрации Джон выбрал два столбца таблицы TRANS, модифицированных функцией XMLFOREST:

select xmlforest(trans_id,trans_type)
from trans;

XMLFOREST(TRANS_ID,TRANS_TYPE)
----------------------------------------------------------------

<TRANS_ID>1</TRANS_ID><TRANS_TYPE>D</TRANS_TYPE>
<TRANS_ID>2</TRAN_ID><TRANS_TYPE>C</TRANS_TYPE>
.
.
.

Функция XMLELEMENT формирует из элементов, выданных функцией XMLFOREST, записи. Для обрамления записей Джон использует тег <TransRec>:

select xmlelement("TransRec",xmlforest(
trans_id,trans_type)) from trans;
XMLELEMENT("TRANSREC",XMLFOREST(
TRANS_ID,TRANS_TYPE))
------------------------------------------------------------------------------
<TransRec><TRANS_ID>1</TRANS_ID>
<TRANS_TYPE>D</TRANS_TYPE>
</TransRec>
<TransRec><TRANS_ID>2</TRANS_ID>

<TRANS_TYPE>C</TRANS_TYPE>
</TransRec>
.
.
.

Используя эти две функции, Джон демонстрирует SQL-скрипт, показанный на листинге 2, который преобразует данные таблицы TRANS и вставляет их в таблицу ARCH_TRANS.

	1	insert into arch_trans
	2	(
	3		arch_date,
	4		trans_date,
	5		trans_details
	6	)
	7	select
	8		sysdate,
	9		trans_date,
	10		xmlelement("TransRec",
	11			xmlforest(
	12				trans_id,
	13				trans_type,
	14				trans_amount
	15			)
	16		)
	17	from trans

Преобразованные столбцы таблицы TRANS, хранятся в XML-формате в столбце TRANS_DETAILS таблицы ARCH_TRANS. После вставки данных в таблицу ARCH_TRANS Джон выбирает их, как это показано на листинге 3, чтобы продемонстрировать, как эти данные хранятся. Обратите внимание, чтобы выводить длинные строки данных, Джон использует команду SET LONG (по умолчанию выводятся только первые 80 символов).

SQL> set long 999999999
SQL> select * from ARCH_TRANS;

ARCH_DATE	    TRANS_DAT		   TRANS_DETAILS
----------------    --------------------   ------------------------------
07-JAN-06           27-DEC-05  		   <TransRec>

                       			   <TRANS_ID>80</TRANS_ID>
                        		   <TRANS_TYPE>D</TRANS_TYPE>
                        		   <TRANS_AMOUNT>4142.68</TRANS_AMOUNT>
                    			   </TransRec>

Когда наступает время архивирования устаревшей секции, Джон преобразовывает эту секцию таблицы ARCH_TRANS в отдельную таблицу:

alter table arch_trans
exchange partition jan2003
with table arch_trans_jan2003
/

Создается таблица ARCH_TRANS_JAN2003 в том же самом табличном пространстве, в котором находится секция JAN2003. Джон может узнать его имя, используя этот запрос:

select tablespace_name
from dba_tab_partitions
where table_name = 'TRANS'
and partition_name = 'JAN2003';

TABLESPACE_NAME
<11p class="bodycopy">---------------------------------
JAN2003

Джон переносит табличное пространство JAN2003, в котором находится таблица ARCH_TRANS_JAN2003.

expdp \"/ as sysdba\"
transport_tablespaces=jan2003 dumpfile=jan2003.dmp

И наконец, Джон удаляет преобразованную таблицу (ARCH_TRANS_JAN2003) и соответствующую секцию как из промышленной таблицы (TRANS), так и из архивной таблицы (ARCH_TRANS):

drop table arch_trans_jan2003;

alter table trans
drop partition jan2003;

alter table arch_trans
drop partition jan2003;

Восстановление данных

Джон демонстрирует программистам и администраторам базы данных компании Acme, как легко архивировать и извлекать таблицу; простота восстановления таблицы - другое важное требование к процессу архивирования. Чтобы показать это, Джон набросал шаги "реверсирования" действий, выполненных во время архивирования.

Сначала он перенес табличное пространство назад в базу данных:
impdp dump_file=jan2003.dmp
transport_datafiles='/u01/jan2003.dbf'

Это незамедлительно делает таблицу ARCH_TRANS_JAN2003 доступной в среде базы данных. Теперь можно выполнять запросы к этой таблице или же объединить ее с главной таблицей (ARCH_TRANS) в виде секции, а потом выполнять запросы. Для объединения таблиц Джон выполнил:

alter table arch_trans
exchange partition jan2003
with table arch_trans_jan2003
/

Теперь в таблице ARCH_TRANS содержатся также и данные за январь 2003. Восстановление завершено.

Выполнение запросов к архивным данным

Программисты и администраторы базы данных компании Acme по-прежнему были озабочены. Они напомнили Джону, что данные в таблице ARCH_TRANS хранятся в XML-формате, а не в реляционном.

Для преобразования XML-данных в реляционный формат Джон написал запрос, показанный на листинге 4. В этом запросе для извлечения всех столбцов из XML-представления данных используется нотация языка путей XPath. Приложения компании Acm будут видеть архивные данные такими, как если бы они были реляционными, и эти приложения не нужно изменять. Присутствующие разработчики были удовлетворены представленным решением.

col Trans_ID format a5
col Trans_Type format a1
col Trans_Amount format 999999.99
select arch_date, trans_date,
extractvalue(trans_details,'/TransRec/TRANS_ID') Trans_ID,
extractvalue(trans_details,'/TransRec/TRANS_TYPE') Trans_Type,
to_number(extractvalue(trans_details,'/TransRec/TRANS_AMOUNT')) Trans_Amount
from arch_trans;

Ключевой элемент этого запроса - функция EXTRACTVALUE, которая извлекает значение элемента из XML-документа или XML-данных. Джон напомнил всем, что в данные столбца TRANS_DETAILS включены XML-теги, которые показывают происхождение данных. Например, данные в одной из записей выглядят так:

<TransRec>
  <TRANS_ID>80</TRANS_ID>

  <TRANS_TYPE>D</TRANS_TYPE>
  <TRANS_AMOUNT>4142.68
</TRANS_AMOUNT>
</TransRec>

Для получения значения элемента TRANS_TYPE Джон начинает с элемента высшего уровня - TransRec, а затем использует нотацию языка Xpath для перехода к нужному уровню:

extractvalue(trans_details,'/TransRec/TRANS_TYPE')

Здесь Джон обращает внимание на важный момент: в отличие от языка SQL имена в языке XML зависят от регистра, так что имена TransRec и transRec - разные имена.

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

create index
  in_arch_trans_type
on arch_trans (
  extractvalue (
    TRANS_DETAILS,
    '/TransRec/TRANS_TYPE'));

Джон поясняет, что этот SQL-оператор создает индекс, похожий на индекс по ключу-функции, но на самом деле этот индекс основан на нотации языка XPath.

Изменения структуры

Теперь осталось только одно критическое требование: возможность изменения структуры промышленной таблицы. Джон показывает пример добавления к таблице TRANS столбца ACC_NO (к этому времени устаревшие данные уже были заархивированы и удалены из таблицы):

alter table trans add (acc_no varchar2(10))

Теперь в таблице TRANS появился новый столбец ACC_NO, но в столбце TRANS_DETAILS таблицы ARCH_TRANS такой элемент отсутствует. Джон заверяет, что вставка данных из столбца TRANS_DETAILS в таблицу TRANS будет выполняться успешно.

Когда добавляется новый столбец, нужно только модифицировать скрипт вставки данных, чтобы он отражал наличие этого нового столбца. Новый скрипт показан на листинге 5. Он идентичен скрипту на листинге 2, исключая строку 15, в которой задается выборка нового столбца ACC_NO.

1  	insert into arch_trans
2	  (
3		arch_date,
4		trans_date,
5		trans_details
6	  )
7	  select
8		sysdate,
9		trans_date,
10		xmlelement("TransRec",
11			xmlforest(
12				trans_id,
13				trans_type,
14				trans_amount
15				acc_no
16			)
17		)
18	  from trans;

Чтобы показывать этот новый столбец, Джон также модифицировал и скрипт извлечения данных (см. листинг 6).

select arch_date, trans_date,
extractvalue(trans_details,'/TransRec/TRANS_ID') Trans_ID,
extractvalue(trans_details,'/TransRec/TRANS_TYPE') Trans_Type,
to_number(extractvalue(trans_details,'/TransRec/TRANS_AMOUNT')) Trans_Amount,
extractvalue(trans_details,'/TransRec/ACC_NO') Acc_No
from arch_trans;

И снова этот скрипт идентичен скрипту на листинге 4, исключая появление имени нового столбца ACC_NO.

Некоторые администраторы базы данных и разработчики компании Acme обратили внимание, что в более старых записях в столбце TRANS_DETAILS отсутствуют значения ACC_NO, они появляются только в более новых записях. Они спросили, как же будут выполняться операторы SELECT?

Джон ответил, что гибкость средств XML позволяет ссылаться на отсутствующие элементы. В таком случае функция EXTRACTVALUES возвратит неопределенное значение NULL. Джон выполнил запрос, показанный на листинге 6, и выделил из вывода две записи, которые показаны на листинге 7. Для первой записи выдается значение столбца ACC_NO как NULL, поскольку в ней нет этого значения столбца. Для второй же записи показывается значение столбца ACC_NO таким, каким оно было введено

ARCH_DATE TRANS_DAT TRANS T TRANS_AMOUNT ACC_NO
--------- --------- ----- - ------------ ---------
01-JAN-06 20-OCT-05 100   C 5740.29
01-FEB-06 07-JAN-06 101   X  100.03      M101

Аналогичным образом, из таблицы могут также удаляться столбцы, в таком случае XML-функция будет возвращать их фактические значения, если они присутствуют в архивной записи и NULL, в противном случае. Это позволяет модифицировать структуру главной таблицы, тогда как архивирование данных и их восстановление будут оставаться "легким делом". Фактически, со временем могут исчезнуть все столбцы (заменены на новые), но архивирование данных с использованием XML-формата будет гарантировать их доступность для тех же самых SQL-операторов. По аудитории прокатился одобрительный шумок.

Заключение

В конце Джон снова вернулся к исходным требованиям к процессу архивирования и показал, как они удовлетворяются предложенными решения (см. таблицу 1). Никаких вопросов и беспокойства больше не было.

• Обратиться в "Интерфейс" за дополнительной информацией/по вопросу приобретения продуктов
• Подробнее о продуктах Oracle
• Приобрести продукты Oracle в ITShop.ru
• Курсы по продуктам Oracle