Общая методология
		Новости и события
		Опыт пользователей
		Oracle Applications
		Высокие технологии
		Постоянная экспозиция
		Мастерская разработчика
		Кабинет администратора
		Полезные ссылки
		Архив

Издатель:
Algorithm Online

При поддержке
ORACLE в России


Техническая
поддержка

официальный
дистрибутор
ORACLE

МАСТЕРСКАЯ РАЗРАБОТЧИКА

Расширения функциональности декларативных ограничений целостности в СУБД Oracle8

1 июля 2000 г.

(Oracle8's Integrity-Constraint Enhancements, by Steve Bobrowski (68fast.html))

Стивен Бобровски

Используйте усовершенствования Oracle8 для обеспечения дополнительной гибкости и расширения функциональности ограничений целостности.

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

СУБД Oracle7 поддерживает различные типы ограничений целостности, включая:

• первичные и уникальные ключи, для обеспечения целостности сущности (всей таблицы);
• ограничения Not Null и Check, для обеспечения целостности доменов (областей определения);
• внешние ключи для обеспечения ссылочной целостности (см. "Краткий обзор ограничений целостности").

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

1. "Откладываемые" ограничения целостности

Oracle7 при выполнении каждого SQL-запроса, изменяющего табличные данные, проверяет, удовлетворяют ли данные всем включенным ограничениям целостности. Непосредственная (Immediate – мгновенная) проверка ограничений целостности усложняет задачу реализации некоторых выполняемых приложениями действий, например, “каскадные обновления”. Каскадное обновление происходит тогда, когда обновляются значения первичных ключей записей главной таблицы, для которой существуют детальные записи. Для сохранения взаимосвязи между главной и детальными записями обновление первичного ключа должно повлечь обновление полей внешнего ключа во всех связанных детальных записях. Так как обновление значений первичных ключей в большинстве приложений не производится, Oracle декларативно (т.е. как часть определения таблицы) не поддерживает каскадное обновление внешних ключей. Но если возможность обновления значений первичного ключа при наличии зависимых детальных записей все же нужна для работы приложения, необходимо реализовать функциональность каскадного обновления с помощью механизма триггеров базы данных, как части приложения. Иначе будет получено сообщение об ошибке:

ORA-02292:
     integrity constraint (...) violated-child record found
(ORA-02292: нарушено ограничение целостности (...) 
- обнаружена порожденная запись.)

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

1. Отключение внешнего ключа детальной таблицы.
2. Обновление значений первичного ключа в главной таблице.
3. Обновление соответствующих значений внешнего ключа в детальной таблице.
4. Повторное включение внешнего ключа на детальной таблице.

В листинге 1 приведен пример реализации этого алгоритма для таблиц CUSTOMERS и ORDERS (ЗАКАЗЧИКИ и ЗАКАЗЫ). Хотя этот метод решает задачу, он сложен в реализации. Приложение должно быть спроектировано так, чтобы оценивать, когда необходимо произвести отключение ограничений целостности, если транзакция производит их обновление. Добавим, что приложение не должно отключать внешние ключи во всех тех случаях, когда обновляемое значение первичного ключа не имеет соответствующих ему детальных записей. Применение этого метода не только придает значительную сложность приложению, но это решение просто не применимо во многих случаях, потому что детальная таблица остается незащищенной от ввода неправильных данных на все то время, когда внешний ключ остается отключенным. Таким образом, непосредственная проверка ограничений целостности в Oracle7 может привести к появлению сложно разрешимых проблем.

Для снижения сложности в Oracle8 реализована новая возможность, описанная в стандарте ANSI/ISO SQL92, называемая "отложенная проверка ограничений целостности". Откладываемое ограничение целостности можно настроить так, чтобы оно проверялось при завершении транзакции, а не при выполнении каждого SQL-запроса. Откладываемые ограничения целостности позволяют создавать временные несоответствия данных ограничениям на время выполнения транзакции. Тем не менее, при фиксации транзакции, данные должны удовлетворять всем ограничениям, иначе Oracle8 произведет ее откат. Это нововведение упрощает реализацию каскадных обновлений.

Для определения откладываемого ограничения целостности используются новые ключевые слова в командах создания и изменения определения таблицы. Новая возможность Oracle8 доступна при определении ограничений целостности, как на уровне столбцов (column-level), так и на уровне таблиц (table-level):

CONSTRAINT ...
[ { [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
| [INITIALLY {IMMEDIATE|DEFERRED}] [[NOT] DEFERRABLE] } ]

По умолчанию Oracle8 создает ограничения целостности не откладываемыми (как и Oracle7). Для создания откладываемого ограничения при его объявлении надо использовать ключевое слово DEFERRABLE без NOT. Если транзакция не начинается с команды SET CONSTRAINTS, то для откладываемого ограничения Oracle использует стандартный алгоритм проверки. При этом Oracle8 проверяет данные на соответствие ограничению в конце каждой команды языка манипулирования данными (DML), как и при использовании ключевых слов INITIALLY IMMEDIATE. Если при определении ограничения были использованы ключевые слова INITIALLY DEFERRED, Oracle8 проверяет данные на соответствие ограничениям в конце каждой транзакции.

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

Чтобы явно определить использование в транзакции одного или сразу нескольких откладываемых ограничений, приложение должно начать транзакцию с новой команды SQL SET CONSTRAINT (или SET CONSTRAINTS):

SET CONSTRAINT[S]
{ [schema.]constraint [,[schema.]constraint] ...  | ALL }
{ IMMEDIATE | DEFERRED }

Синтаксис команды позволяет указать вид проверки, как для нескольких определенных ограничений, так и для всех ограничений, которые могут быть в SQL-запросах данной транзакции. Если в транзакции откладываются одно или нескольких ограничений, можно также использовать команду SET CONSTRAINTS ALL IMMEDIATE перед фиксацией текущей транзакции для предварительной проверки базы данных на соответствие отложенным ограничениям:

• Если команда выполнилась успешно, то изменения сделанные транзакцией удовлетворяют всем отложенным ограничениям и можно успешно завершить транзакцию.
• Если при выполнении команды произошла ошибка, значит, изменения произведенные транзакцией, привели к появлению данных, не удовлетворяющих одному или нескольким отложенным ограничениям. Для успешного фиксирования транзакции необходимо произвести дополнительные изменения в базе данных, чтобы данные не противоречили отложенным ограничениям, и выполнить повторную проверку с помощью команды SET CONSTRAINTS ALL IMMEDIATE. Если этого не сделать, при завершении транзакции Oracle произведет аварийный откат сделанных ею изменений.

Ранее обозначенная проблема каскадных обновлений, представляет собой хороший пример уместного использования откладываемых ограничений. Прежде всего, необходимо определить таблицу ORDERS с ее внешним ключом на таблицу CUSTOMERS, как откладываемое ограничение целостности, которое Oracle8 будет проверять по умолчанию:

CREATE TABLE orders
      (ord_id INTEGER
      CONSTRAINT pkey_orders   PRIMARY KEY,
      cust_cust_id INTEGER REFERENCES customers
      DEFERRABLE INITIALLY IMMEDIATE,
      orderdate DATE,
      shipdate DATE,
      paiddate DATE,
      status CHAR(1));

При использовании откладываемого ограничения целостности алгоритм выполнения каскадного обновления представляется предельно простым:

SET CONSTRAINTS ALL DEFERRED;
     UPDATE customers
      SET cust_id = cust_id + 100;
 -- update the foreign keys
     UPDATE orders
      SET cust_cust_id = cust_cust_id + 100;
     COMMIT;

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

2. Неуникальные индексы для первичных и уникальных ключей

По умолчанию, при создании первичного или уникального ключа на таблице, как Oracle7, так и Oracle8 создают уникальный индекс для поддержки каждого такого ограничения целостности. Например, предыдущая команда CREATE TABLE создает не только таблицу ORDERS, но и индекс (PKEY_ ORDERS) для поддержки объявленного первичного ключа.

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

К сожалению, при работе с таблицами размером в несколько гигабайт, отключение и повторное включение первичного или внешнего ключа может оказаться неприемлемым: как Oracle7, так и Oracle8 удаляют соответствующий уникальный индекс, который сервер использует для поддержки ограничения целостности. Это означает, что при повторном включении ограничения после загрузки данных, СУБД должна будет пересоздать соответствующий уникальный индекс, что может занять часы для больших таблиц.

Кроме того, на таблицу накладывается монопольная блокировка (exclusive lock)на все время пересоздания индекса, что делает таблицу недоступной для конкурентного изменения данных и других операций.

Oracle8 позволяет пользователю самостоятельно создать неуникальный индекс для поддержки первичного или уникального ключа. Вместо того чтобы позволить серверу автоматически создать индекс, вы можете создать его для отключенного ограничения целостности самостоятельно. Затем, при повторном включении ограничения, Oracle8 будет использовать это индекс. Следующие команды демонстрируют использование этой возможности Oracle8.

CREATE TABLE orders
(ord_id INTEGER CONSTRAINT pkey_orders PRIMARY KEY DISABLE,
... ;
     CREATE INDEX pkey_ord_id ON orders (ord_id);
     ALTER TABLE orders ENABLE PRIMARY KEY; 

Теперь, при отключении первичного ключа таблицы ORDERS, индекс PKEY_ORD_ID останется. Запросы, которые будут выполняться в то время, когда ограничение отключено, смогут использовать этот индекс. Далее, при повторном включении первичного ключа, индекс уже будет существовать, и СУБД не придется его перестраивать с нуля, что позволит значительно ускорить операцию.

3. Мгновенное включение ограничения

Для ускорения включения отключенного ограничения целостности, в СУБД Oracle8 расширена функциональность предложения CONSTRAINT команды ALTER TABLE:

CONSTRAINT ... ENABLE [VALIDATE|NOVALIDATE]

По умолчанию, или когда указывается ключевое слово VALIDATE, Oracle8 проверяет целостность всех данных таблицы перед включением ограничения (совпадает с поведением Oracle7).

Чтобы проверить целостность всех данных таблицы, Oracle8 накладывает монопольную блокировку (Exclusive Lock) на таблицу, которая не только не позволяет конкурентное обновление данных на все время проверки, но позволяет включать лишь по одному ограничению целостности одновременно. При работе с большими таблицами, повторное включение одного за другим всех ограничений целостности после пакетной загрузки существенных объемов данных может занять много времени.

При включении ограничения с ключевым словом NOVALIDATE, Oracle8 не проверяет имеющиеся в таблице данные на соответствие ограничению целостности. Таким образом, Oracle8 может сразу включить проверку ограничения целостности для последующих транзакций. После включения всех ограничений на таблице вы можете использовать ту же команду с ключевым словом VALIDATE для перевода каждого ограничения в “проверенное” состояние. Перевод из "непроверенного" в "проверенное" состояние не требует монопольной блокировки таблицы, таким образом, таблица продолжает поддерживать конкурентные транзакции. Кроме того, в Oracle8 для ускорения проверки данных на соответствие ограничению используется внутренняя параллельная обработка. Следующая последовательность команд демонстрирует, как можно оптимально включить отключенное ограничение целостности:

ALTER TABLE orders DISABLE PRIMARY KEY; 
-- Операция пакетной загрузки большого объема данных.
ALTER TABLE orders ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE orders ENABLE VALIDATE PRIMARY KEY;

Заключение

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

• Вы можете создать откладываемые ограничения целостности и, затем, отложить их проверку до конца транзакции с помощью новой SQL-команды SET CONSTRAINTS. Возможность откладывать проверку условий ограничений облегчает реализацию таких требований сложных систем обработки данных, как каскадное обновление.
• Вы можете усилить первичные и уникальные ключи, создав соответствующие неуникальные индексы. Когда вы отключите такое ограничение, Oracle8 не удалит индекс, ускоряя, таким образом, последующее включение ограничения.
• Вы можете включить ограничение в "не проверенном" состоянии для того, чтобы сразу обеспечить проверку ограничения для изменяемых данных и позволить конкурентные обновления данных таблицы. В последствии, вы можете включить ограничение целостности в "проверенном" состоянии, чтобы гарантировать, что все данные таблицы ему удовлетворяют.

Стивен Бобровски главный администратор OraWorld (www.oraworld.com). Автор книг "Архитектура Oracle8" (Oracle Press, 1997) и "Oracle7 и вычисления клиент/сервер" (Sybex, 1996).

Листинг 1. SQL-предложения для временного отключения ограничений

	Материал номера: Новый тест для специалистов по Oracle
	Колонка главного редактора: Oracle открывает третье тысячелетие.  Письмо в редакцию
	Человек месяца: Беседа с руководителями ИВЦ АИС и WEB-центра “Омега” Oracle: от МВД до РПЦ