Ограничения целостности. Представления данных

Ограничения целостности для представлений данных

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

Запрет непосредственных обновлений

При создании представления можно запретить изменение данных БД через него непосредственно командами DML:

CREATE VIEW employeers ( name, location ) 
AS 
   SELECT ename, loc
   FROM   emp, dept
   WHERE  emp.deptno = dept.deptno
WITH READ ONLY
;

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

Для ограничения READ ONLY у виртуальных таблиц не предусмотрено собственное именование конструкцией CONSTRAINT, и оно всегда получает системное название в перечне в USER_CONSTRAINTS.

Сужение возможности непосредственных обновлений

Указание WITH CHECK OPTION при определении представления не запрещает применять к нему операции INSERT, UPDATE и DELETE вообще, а запрещает только те изменения, которые совершатся в подлежащих таблицах, но не будут обнаруживать себя в данных самого представления. В то же время в отсутствие такого указания ненаблюдаемые через представление изменения в БД будут допускаться. Создадим представление о сотрудниках отдела 10:

CREATE VIEW emp10 
AS 
   SELECT *
   FROM   emp
   WHERE  deptno = 10
;

Получаем:

SQL> INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 20 );
1 row created.
SQL> SELECT empno, deptno FROM emp10 WHERE empno = 1111;
no rows selected
SQL> SELECT empno, deptno FROM emp WHERE empno = 1111;
     EMPNO     DEPTNO
---------- ----------
      1111         20
SQL> ROLLBACK;
Rollback complete.

То есть, предъявив INSERT к представлению EMP10, мы добавили в таблицу EMP сотрудника, которого само представление нам не показывает.

Изменим определение EMP10:

CREATE OR REPLACE VIEW emp10 
AS 
   SELECT *
   FROM   emp
   WHERE  deptno = 10
WITH CHECK OPTION 
CONSTRAINT only_dept10
;

Получаем:

SQL> INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 20 );
INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 20 )
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 10 );
1 row created.
SQL> ROLLBACK;
Rollback complete.

Для ограничения CHECK OPTION у представлений предусмотрено собственное именование конструкцией CONSTRAINT, что выше и сделано. Без этого ограничение получит в USER_CONSTRAINTS системное название.

Материализованные (овеществленные) представления данных

"Материализованные" (овеществленные) представления данных (materialized views) появились в версии Oracle 8.1 как вариация одноименной категории объектов БД, предлагаемой стандартом SQL. Аналогично обычным представлениям, они предполагают хранение формулировки запроса SELECT к таблицам-источникам, однако вдобавок сохраняют и сам результат запроса в виде хранимой таблицы. Делается это с основною целью обеспечить более быстрый, или же попросту надежный доступ к данным, ввиду отсутствия необходимости вычислять подзапрос по ходу обработки основного запроса и возможности вместо этого взять уже посчитанный результат. Оборотной стороной является усложнение техники работы с данными, неизбежное вследствие раздвоения, возникающего на логическом уровне схемы (источники подзапроса — посчитанный результат). Так, для этого рода объектов следует предусмотреть и обеспечить синхронизацию данных.

Двумя основными областями использования материализованных представлений данных являются следующие.

• Локальное хранение данных, отобранных из таблиц в других базах в сети. По старой терминологии (до версии 8.1) такие локальные выжимки удаленных данных назывались snapshots, то есть "снимки". В целях обратной совместимости слово snapshot продолжает существовать в некоторых командах и названиях в Oracle до сих пор, наряду с materialized view; по сути, эти два термина сегодня в Oracle синонимичны.
• Устройство вспомогательных таблиц-"спутников" у особенно больших таблиц, ради хранения подсчитанных заранее обобщений (GROUP BY и агрегатные значения). Такое применение характерно для БД, спроектированых по типу "склада данных" (data warehouse) в системах поддержки принятия решений и анализа данных.

Материализованные представления создаются, переопределяются и удаляются командами {CREATE | ALTER | DROP} MATERIALIZED VIEW …, например:

CREATE MATERIALIZED VIEW jobsal 
AS 
  SELECT job, SUM ( sal ) AS sum_sal
  FROM   emp 
  GROUP  BY job
;

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

Реляционная теория предпочитает различать понятия snapshot и materialized view. Более того, термин materialized view она считает некорректным (и даже логически противоречивым), полагая правильным употребление на его месте snapshot, ввиду того что именно в названии snapshot заложено возможное отставание "материализованных" данных от текущего состояния таблиц-источников.

Особенности именованых представлений данных

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

Эффективность же их использования в БД определяется следующими общими факторами:

• Простые представления не предоставляют данные немедленно из базы. Получение данных в них требует вычислений и работы СУБД.
• Простые представления не предъявляют требований к месту в БД, связанных так или иначе с объемами представляемых данных; память в БД расходуется только на хранение в словаре-справочнике определений представлений.
• Материализованные представления данных позволяют ускорить обращение к данным в БД.
• Материализованные представления данных требуют в БД дополнительного места для хранения результатов запроса к базе.
• Материализованные представления несут с собой риски рассинхронизации и рассогласования данных и требуют в связи с этим определенных организационных хлопот.

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

(Неименованые) представления данных, встроенные в запрос, не создаются как хранимые в БД объекты, а используются только в виде составных частей запросов SQL. Оригинальное название — inline view, то есть представления, "встроенные в текст" основного запроса.

Формально неименованое представление данных — это подзапрос, подставленный на место источника данных в SELECT или в команды DML на изменение данных. Примеры применения во фразе FROM предложения SELECT приводились выше и относительно очевидны.

Пример неименованого представления данных в операторе изменения значений UPDATE:

UPDATE ( SELECT e.ename, d.dname 
         FROM   emp e INNER JOIN dept d
                USING ( deptno )
         WHERE  d.dname = 'SALES'
         AND    e.comm IS NULL
       ) 
SET ename = INITCAP ( ename )
;

Обратите внимание, что вложенный SELECT ("неименованое представление данных") делает выборку из двух таблиц. Следовательно, этот оператор UPDATE не сводим к привычно применяемому к одной таблице, по крайней мере простым образом.

Подзапросы подобного рода объединяет с обычными представлениями то обстоятельство, что они не допускают автоматически возможность "изменений" всех своих столбцов. Такие "изменения" иногда делать можно (пример выше), а иногда нельзя. Правила разрешения правки значений как раз совпадают с существующими для обычных представлений. Если программисту их потребуется в конкретных обстоятельствах уточнить, ему достаточно создать на основе подзапроса обычное представление и справиться о возможности правки полей в USER_UPDATABLE_COLUMNS. Однако если такой подзапрос эпизодический и не требует сохранения в БД, Oracle позволяет попросту вставить его в оператор DML указанным выше способом.

Упражнение. Верните именам сотрудников отдела продаж, не получающих комиссионные, первоначальное написание заглавными буквами. Замените "SET ename = INITCAP (ename)" на "SET dname = INITCAP (dname)" и попробуйте повторить запрос. Замените порядок указания таблиц во фразе FROM и вновь повторите запрос.

Еще примеры:

DELETE 
       FROM   emp e INNER JOIN dept d
              USING ( deptno )
       WHERE  e.job <> 'CLERK'
WHERE deptno = 10
;
INSERT 
INTO ( SELECT e.empno, deptno
       FROM   emp e INNER JOIN dept d
              USING ( deptno )
       WHERE  e.job NOT IN ( 'CLERK', 'SALESMAN', 'MANAGER' )
       WITH CHECK OPTION
     )
VALUES ( 1111, 10 )
;

Упражнение. В последнем операторе замените VALUES ( 1111, 10 ) на VALUES ( 1112, 20 ) и повторите запрос. Следом уберите указание WITH CHECK OPTION и снова повторите. Объясните результаты.

Вернем значения EMP:

ROLLBACK;