Материализованные представления, как средство контроля целостности данных

Контроль целостности данных — одна из важнейших функций СУБД. Чем тщательнее этот контроль организован, тем проще реализовывать прикладную логику, ведь чем больше ограничений контролируется базой данных, тем меньше вариаций «а что, если» следует предусмотреть при реализации логики. В то же самое время контроль целостности оказывается достаточно удобно использовать и для проверки корректности работы прикладного слоя. Что-то вроде юнит-тестов. «Лишняя» проверка, порой может сослужить очень добрую службу.

Традиционный набор ограничений — ограничение первичного, внешнего ключей, уникальности при использовании нормализации позволяет удовлетворить подавляющее большинство случаев потребности контроля. Однако в случае, когда ограничение оказывается зависимым от значений в нескольких таблицах и строках, этих средств оказывается недостаточно. Такие ограничения приходится реализовывать триггерной логикой. И реализация далеко не всегда оказывается проста. Разработчику приходится держать в уме то, что модификация данных может проводиться в конкурентной среде, потому необходимо самостоятельно заботиться о блокировании ресурсов, при этом, еще и пытаясь избегать взаимных блокировок. Реализация ограничения строки может потребовать доступа к другим строкам этой же таблицы, что, в свою очередь является ограничением платформы — Oracle не позволяет обращаться к изменяемому в настоящее время(мутирующему) набору данных.

Но есть и другой путь. В некоторых случаях оказывается возможным использование ограничений, наложенных на материализованные представления, обновляемые по факту фиксации транзакций (fast refresh on commit). Такие ограничения будут работать как отложенные (deferred) и не будут позволять зафиксировать транзакцию, если вдруг целостность данных оказалась нарушенной. В рамках же модифицирующей транзакции ограничения могут нарушаться. С одной стороны это упрощает модификацию данных, с другой, мешает идентифицировать источник ошибки. В этой статье я хотел бы привести пару простых примеров реализации таких ограничений.

Постановка задачи

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

Пусть мы имеем необходимость учета товара в разрезе зоны размещения. Размещением в данном случае выступает магазин(S) или склад(W).

Зона — территория физическая или логическая каждого конкретного размещения. К примеру — торговый зал, или же даже полки торгового зала, материальная комната, холодильник, зона утерянного товара. Каждое размещение может иметь более одной зоны каждого типа, но одна зона каждого типа обязательно должна быть помечена как основная. Она будет использоваться по умолчанию, если зона операции явно не определена. Основная зона должна быть одна и только одна для каждого типа зоны. Это будет первый вид ограничения, который мы попытаемся реализовать.

Второй тип ограничения — состав зон. В нашем примере ограничимся лишь заданием правил для трех типов зон:

• Зона хранения (K) — зона в которой осуществляется хранение товара эта зона обязательна для склада, но может быть определена и для магазина.
• Зона торгового зала(S) — обязательна для магазина и не может быть определена для склада
• Зона утерянного товара(L) — логическая зона, на которую будет перемещаться товар, статус которого не ясен, требует прояснения. Обязательна и для склада и для магазина

Каждая запись о размещении может находиться в трех состояниях — черновик (W), активная (A), не активная (I). Когда запись находится в состоянии «Черновик», мы даем пользователю наибольшую свободу действий и позволяем нарушать это ограничение.

Реализация

таблицы

	create table location (
	  loc number primary key
	  ,loc_type varchar2(1 char) check (loc_type in ('S','W'))
	  ,status varchar2(1 char) not null check (status in ('W','A','I'))
	  ,loc_desc varchar2(200 char)
	);
	create table zone(
	  zone number primary key
	  ,loc number references location(loc)
	  ,is_pirmary varchar(1 char) not null check (is_pirmary in ('Y','N'))
	  ,zone_type varchar2(1 char) not null 
	  ,zone_desc varchar2(200 char)
	);

	insert into location values (1,'S','W','Магазин 1 без ошибок');
	insert into zone values (1,1,'Y','K','Зона хранения магазина 1');
	insert into zone values (2,1,'Y','S','Торговый зал магазина 1');
	insert into zone values (3,1,'Y','L','Зона утерянного товара магазина 1');
	commit;
	

Ограничение количества основных зон

Для реализации этого ограничения создадим материализованное представление, которое будет производить подсчет основных зон для каждого типа зоны размещения, и сверху на него наложим ограничение, контролирующее строгое равенство единице рассчитанного значения. Для запросов, на основе которых строятся материализованные представления определен целый ряд ограничений, который, к тому еще сильно ужесточается наложением требований к обновлению методом fast. В нашем случае мы имеем аггрегирующее материализованное представление, а потому должны создать materialized view log для таблицы zone, включающий rowid и новые значения, список полей которого должен включить все значения, которые могут повлиять на результат запроса

create materialized view log 
  on zone with rowid
               ,sequence
               (zone,loc,zone_type,is_primary) 
               including new values

Так же мы обязаны включить в результат, возвращаемый запросом значение «count(*)»

	create materialized view mv$zoneloc_pimary$chk
	  refresh fast on commit
	as
	select loc
	       ,zone_type
	       ,count(decode(is_primary,'Y',1)) primary_count 
	       ,count(*) cnt
	  from zone 
	  group by loc,zone_type;
	

Здесь следует отметить: для того чтобы оценить, может ли материализованное представление построенное по запросу использовать для обновления метод fast, существует процедура dbms_mivew.explain_mview. Крайне желательно использовать ее для контроля доступен ли для представления метод обновления fast. К примеру, если бы мы забыли указать в запросе count(*), материализованное представление с успехом бы создалось и корректно работало при выполнении операции вставки. Однако при модификации, удалении значение primary_count — не пересчитывалось бы, что нарушило бы логику нашего ограничения. Однако если мы используем explain_mview, оракл услужливо подскажет нам наш просчет.

	SQL> set serveroutput on
	SQL> declare
	  2    result SYS.ExplainMVArrayType;
	  3  begin
	  4    dbms_mview.explain_mview(mv => 'select loc
	  5                                         ,zone_type
	  6                                         ,count(decode(is_primary,''Y'',1)) primary_counnt
	  7                                         --,count(*) cnt
	  8                                    from zone
	  9                                    group by loc,zone_type'
	 10                             ,msg_array => result
	 11                            );
	 12    for i in 1..result.count
	 13    loop
	 14      dbms_output.put(rpad(result(i).capability_name,30,' '));
	 15      dbms_output.put(' '||result(i).POSSIBLE);
	 16      dbms_output.put(' '||result(i).MSGTXT);
	 17      dbms_output.put_line(null);
	 18    end loop;
	 19  end;
	 20  /
	 
	PCT                            F 
	REFRESH_COMPLETE               T 
	REFRESH_FAST                   T 
	REWRITE                        T 
	PCT_TABLE                      F relation is not a partitioned table
	REFRESH_FAST_AFTER_INSERT      T 
	REFRESH_FAST_AFTER_ONETAB_DML  F COUNT(*) is not present in the select list
	REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
	REFRESH_FAST_PCT               F PCT is not possible on any of the detail tables in the materialized view
	REWRITE_FULL_TEXT_MATCH        T 
	REWRITE_PARTIAL_TEXT_MATCH     T 
	REWRITE_GENERAL                T 
	REWRITE_PCT                    F general rewrite is not possible or PCT is not possible on any of the detail tables
	PCT_TABLE_REWRITE              F relation is not a partitioned table
	 
	PL/SQL procedure successfully completed
	 

Итак, материализованное представление создано, осталось только добавить ограничение

	alter table mv$zoneloc_pimary$chk 
	   add constraint zone_loc_primary$chk 
	         check (primary_count=1) 
	   deferrable initially deferred;
	

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

Проверим работу этого ограничения

	SQL> insert into location values (2,'S','W','Магазин 2 нарушения ограниченя основной зоны ');
	 1 row inserted
	 SQL> commit;
	 Commit complete
	

Попытаемся создать зону с типом, не имеющем отметки «основной».

	SQL> insert into zone values (4,2,'N','S','Основной торговый зал не определен');
	1 row inserted
	SQL> commit;
	ORA-02091: transaction rolled back
	ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated
	

Попытаемся определить две основные зоны для размещения с одинаковым типом.

	SQL> insert into zone values (5,2,'Y','L','Первая основная зона утерянного товара');
	1 row inserted
	SQL> commit;
	Commit complete
	SQL> insert into zone values (6,2,'Y','L','Вторая основная зона утерянного товара');
	1 row inserted
	SQL> commit;
	ORA-02091: transaction rolled back
	ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated
	

Ограничение состава зон размещения

Это ограничение от предыдущего отличается тем, что опирается на значения не одной таблицы, а двух. Т.е. одновременно необходимо удовлетворить требования метода обновления fast для представлений с соединениями и аггрегирующих представлений. Но это не возможно. Мы не можем одновременно вывести в результат и rowid присоединенной строки и count(*). По этой причине придется строить каскад материализованных представлений. В одном будет производится соединение наборов данных, в другом — аггрегация.

Для начала необходимо создать materialized veiw log для таблицы размещений. Для таблицы зон будет использован ранее созданный лог.

create materialized view log 
  on location with rowid
                   ,sequence
                   (loc,loc_type,status) 
                   including new values;

Следом создаем join mivew. К сожалению ANSI синтаксис здесь оракл не воспринимает, испльзуем old-style join.

	create materialized view mv$location$zone$join 
	  refresh fast on commit
	as
	select l.loc
	       ,l.loc_type
	       ,z.zone
	       ,z.zone_type
	       ,l.rowid l_rowid
	       ,z.rowid z_rowid
	 from location l
	      ,zone z 
	 where z.loc(+) = l.loc
	       and l.status in ('A','I')
	

Создаем materialized veiw log для join представления

create materialized view log 
  on mv$location$zone$join 
    with rowid
         ,sequence
         (loc,loc_type,zone_type) 
         including new values;

Создаем аггрегирующеее материализованное представление

	create materialized view mv$location$zone$agg
	  refresh fast on commit
	as
	select loc
	       ,loc_type
	       ,count(decode(zone_type,'K',1)) K_cnt
	       ,count(decode(zone_type,'S',1)) S_cnt
	       ,count(decode(zone_type,'L',1)) L_cnt
	       ,count(*) cnt
	from mv$location$zone$join
	group by loc,loc_type;
	

ну и сами ограничения

	alter table mv$location$zone$agg 
	  add constraint wh_zones_chk 
	     check(loc_type != 'W' or K_cnt > 0 and S_cnt = 0 and L_cnt > 0) 
	  deferrable initially deferred;
	alter table mv$location$zone$agg 
	  add constraint store_zones_chk 
	     check(loc_type != 'S' or K_cnt >= 0 and S_cnt > 0 and L_cnt > 0)
	  deferrable initially deferred;
	

Проверим работу ограничений:

	SQL> insert into location (loc,loc_type,status,loc_desc)
	  2         values (3,'S','W','Магазин 3 не определены обязательные зоны');
	1 row inserted
	SQL> commit;
	Commit complete
	

Размещение успешно создано в статусе «Черновик». Попробуем активировать его:

	SQL> update location set status = 'A' where loc = 3;
	1 row updated
	SQL> commit;
	ORA-02091: transaction rolled back
	ORA-02290: check constraint (ZTXN.STORE_ZONES_CHK) violated
	

Нет. Нельзя активировать размещение, если для него не определены необходимые для его типа зоны

	SQL> insert into zone(zone,loc,is_primary,zone_type) values (7,3,'Y','S');
	1 row inserted
	SQL> insert into zone(zone,loc,is_primary,zone_type) values (8,3,'Y','L');
	1 row inserted
	SQL> update location set status = 'A' where loc = 3;
	1 row updated
	Commit complete
	

Заключение

Когда это использовать

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

Очень удобно иметь такие ограничения на этапе тестирования, старта, стабилизации проектов, когда не достает доверия к корректности работы логики, а данные могут модифицироваться и мимо прикладного звена.

Когда это не использовать

Очевидно, что не стоит использовать такой подход в случаях, когда он дает заметную просадку по производительности.

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