Автор: Никифоров Дмитрий
Тема: Ограничения целостности, EXCHANGE PARTITION и локальные индексы
Источник: Russian Oracle User Group
Номер документа:8.1
Дата публикации: 16.05.2010
Последнее изменение: 16.04.2010

Как обычно, тему статьи любезно подсказали разработчики приложения. Растущие объемы обрабатываемых данных делают необходимым
активное использование секционированных таблиц. В данном конкретном случае задача, которую решали разработчики, состояла
в следующем. Необходимо было
a) трансформировать структуру таблицы из несекционированной в секционированную, содержащую данные за один месяц в отдельных
партициях
b) организовать ежемесяцчную "очистку" данных - из секции, которая стала "aged out" должны быть удалены все данные, кроме
удовлетворяющих определенному критерию
c) "очистка" должна быть организована таким образом, чтобы как можно больше свободного пространства было возвращено для
повторного использования сегментами

Предложенная схема "очистки" предполагала создание staging table, перенос в нее необходимых данных, обмен очищаемой секции на
staging table и удаление staging table (т.е. фактически, обмененой секции). Во всю эту замечательную схему вкралась только
одна неприятная деталь - таблица, которую предполагалось подвергнуть секционированию и регулярной последующей очистке, со-
держала ограничение первичного ключа и несколько ограничений внешнего ключа.

Начав тестировать код, разработчики немедленно наткнулись на ORA-14130:

SQL> create table emp_part (empno int, deptno int)
  2  partition by list (deptno) (
  3  partition p_10 values(10),
  4  partition p_20 values(20),
  5  partition p_30 values(30),
  6  partition p_40 values(40)
  7  );

Table created.

SQL> insert into emp_part select empno, deptno
  2  from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter table emp_part add primary key (empno, deptno)
  2  using index local;

Table altered.

SQL> alter table emp_part add constraint fk_000
  2  foreign key (deptno) references dept (deptno);

Table altered.

SQL> create table shadow_emp as
  2  select * from emp_part where 1=0;

Table created.

SQL> alter table shadow_emp modify(empno not null, deptno not null);

Table altered.

SQL> create unique index shadow_emp_u
  2  on shadow_emp (empno, deptno);

Index created.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp including indexes;
alter table emp_part exchange partition p_30
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

Заглянув в святцы, девелопмент обнаружил, что Oracle в качестве workaround рекомендует
следующие действия:

ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION 

Cause: One of the tables named in the ALTER TABLE EXCHANGE PARTITION command 
has a UNIQUE constraint for which no matching (vis-a-vis key columns) constraint is defined 
on the other table or a matching constraint is defined on the other table, but it differs from 
that defined on the first table vis-a-vis being enabled and/or validated.

Action: Ensure that for every UNIQUE constraint defined on one of the tables named in the 
ALTER TABLE EXCHANGE PARTITION statement there is a matching (vis-a-vis key columns 
and being enabled and/or validated) UNIQUE constraint defined on the other table. 
If UNIQUE constrains are enabled, UNIQUE constraints on the partitioned table 
should be enforced using local indexes.

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

SQL> alter table shadow_emp add primary key(empno, deptno);

Table altered.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp including indexes;
alter table emp_part exchange partition p_30
*
ERROR at line 1:
ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION

На этот раз документация толковала случай несколько иначе:

ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION 

Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE PARTITION 
statement have different FOREIGN KEY constraints.

Action: Ensure that the two tables do not have FOREIGN KEY constraints defined on any column 
or disable all FOREIGN KEY constraints on both tables. Then retry the operation.

Вот так, ни больше не меньше - "disable all FOREIGN KEY constraints".
Сказано - сделано. Однако тут разработчиков ждала еще одна беда, вполне предсказуемая, если учесть что операция DISABLE
CONSTRAINT является операцией уровня таблицы, а не секции, а над таблицей, секция которой подлежала очистке, производились
активные DML-операции:

SQL> update emp_part set empno = -empno
  2  where deptno = 10
  3  /

3 rows updated.

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   execute immediate 'alter table emp_part disable constraint fk_000';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 4

Следует обратить внимание на два факта. Во-первых, документация почему-то предлагает различные пути решения
схожей проблемы для ограничений первичного и внешнего ключей. Во-вторых, часть фразы "(vis-a-vis key columns and being
enabled and/or validated)" заставляет задуматься о том, что статус VALIDATE/NOVALIDATE также некоторым образом играет
свою роль.

Сомнения в трактовке workaround для FOREIGN KEY вполне обоснованы - так как Oracle просто заинтересован в том, чтобы
получить в результате обмена данные, удовлетворяющие ограничениям целостности, то для разрешения конфликта по FOREIGN KEY
достаточно просто добавить подходящее ограничение в несекционированную таблицу:

SQL> alter table shadow_emp add constraint fk_002
  2   foreign key (deptno) references dept (deptno);

Table altered.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp including indexes;

Table altered.

Т.о., решение вполне естественное - вместо мучительных попыток отключить ограничения на активно используемой таблице куда как
проще создать соответсвующие ограничения на обмениваемой с секцией таблице. Остаются несколько вопросов, а именно:
1) Почему требуется согласование статуса VALIDATE ?
2) Каким образом Oracle убедится в том, что обмениваемые данные "встраиваются" в существующее ограничение
уникальности ? Ведь при выполнении EXCHANGE PARTITION возможно использование опции WITHOUT VALIDATION, индекс, используемый
для поддержки PRIMARY KEY может быть локальным.

Для начала проясним смысл согласования по VALIDATE/NOVALIDATE.
Статус NOVALIDATE указывает на то, что часть данных, присутствующих в таблице, может не удовлетворять ограничению целостности
- например, когда происходила актуализация (ENABLE) ограничения, соответствие уже имеющихся в таблице данных ограничению не
проверялось. Как только мы переводим ограничение в режим VALIDATE, происходит проверка существующих данных на соответствие
ограничению. Вполне логично, что Oracle не позволяет обменять данные, если два аналогичных ограничения находятся в различных
состояниях, поскольку в противном случае в таблицу или секцию, данные в которой были проверены на соответствие ограничению
целостности и являются с этой точки зрения "чистыми", попадут потенциально "грязные" данные, которые могут нарушать ограничение
целостности. Отсюда, кстати, следует, что в рассматриваемом частном случае, когда в секционированной таблице ограничения цело-
стности находятся в состоянии NOVALIDATE, заботиться о наличии соответствующих ограничений в "теневой" таблице вообще не нужно -
Oracle просто игнорирует проверку ограничений:

SQL> drop table emp_part;

Table dropped.

SQL> drop table shadow_emp;

Table dropped.

SQL> create table emp_part (empno int not null, deptno int not null)
  2  partition by list (deptno) (
  3  partition p_10 values(10),
  4  partition p_20 values(20),
  5  partition p_30 values(30),
  6  partition p_40 values(40)
  7  );

Table created.

SQL> insert into emp_part select empno, deptno
  2  from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> alter table emp_part add primary key (empno, deptno)
  2  using index local enable validate;

Table altered.

SQL> alter table emp_part add constraint fk_000
  2  foreign key (deptno) references dept (deptno)
  3  enable validate;

Table altered.

SQL> create table shadow_emp as
  2  select * from emp_part where 1=0;

Table created.

SQL> insert into shadow_emp select empno, deptno
  2  from emp where deptno = 30 and rownum = 1;

1 row created.

SQL> create unique index shadow_emp_u
  2  on shadow_emp (empno, deptno);

Index created.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp including indexes;
alter table emp_part exchange partition p_30
*
ERROR at line 1:
ORA-14128: FOREIGN KEY constraint mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> alter table emp_part modify constraint fk_000 novalidate;

Table altered.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp including indexes;
alter table emp_part exchange partition p_30
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> alter table emp_part modify primary key novalidate;

Table altered.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp including indexes;

Table altered.

Еще один казус связан с применением опции WITHOUT VALIDATION и наличием локального индекса, поддерживающего ограничение первичного
или уникального ключа (или просто с наличием уникального локального индекса). Поскольку секционирование локального индекса идет
по тому же критерию, что и секционирование таблицы, то в этом случае критически важно, чтобы в секцию попадали "правильные" данные.
Иначе возможно нарушение ограничения целостности несмотря на состояние ENABLE VALIDATE и уникальность индекса.

SQL> drop table emp_part
  2  /

Table dropped.

SQL> drop table shadow_emp
  2  /

Table dropped.

SQL> create table emp_part (empno int not null, deptno int not null)
  2  partition by list (deptno) (
  3  partition p_10 values(10),
  4  partition p_20 values(20),
  5  partition p_30 values(30),
  6  partition p_40 values(40)
  7  )
  8  /

Table created.

SQL> create unique index emp_part_u
  2  on emp_part (empno, deptno)
  3  local
  4  /

Index created.

SQL> alter table emp_part add primary key (empno, deptno)
  2  using index emp_part_u
  3  /

Table altered.

SQL> create table shadow_emp as
  2  select * from emp_part where 1=0
  3  /

Table created.

SQL> insert into shadow_emp (empno, deptno) values(-1, 40)
  2  /

1 row created.

SQL> create unique index shadow_emp_u
  2  on shadow_emp (empno, deptno)
  3  /

Index created.

SQL> alter table shadow_emp add primary key (empno, deptno)
  2  using index shadow_emp_u
  3  /

Table altered.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp 
  3  including indexes 
  4  without validation
  5  /

Table altered.

SQL> select * from emp_part partition (p_30)
  2  /

     EMPNO     DEPTNO
---------- ----------
        -1         40

SQL> insert into emp_part (empno, deptno) values(-1, 40)
  2  /

1 row created.

SQL> select partition_name, status from user_ind_partitions
  2  where index_name = 'EMP_PART_U'
  3  /

PARTITION_NAME                 STATUS
------------------------------ --------
P_10                           USABLE
P_20                           USABLE
P_30                           USABLE
P_40                           USABLE

Судя по приведенным результатам использвание локальных индкексов вносит в процесс поддержки constraints определенные нюансы.
Локальные индексы, являющиеся уникальными или использующиеся для поддержки unqiue или primary key, обязаны содержать в своем
составе поля таблицы, входящие в ключ секционирования. При этом, поскольку индекс локально секционирован, при проверке огра-
ничений целостности используется только одна секция локального индекса, ключ секционирования которой соответствует значению
во вставляемой записи. Понятно, что в этом случае такая проверка, являющаяся локальной для секции, ничего не знает о том, что
в другой секции находятся дубликаты, попавшие туда благодаря WITHOUT VALIDATION во время операции обмена. Более того, так как
при исползовании ключа партиционирования в качестве одного из критериев выбора данных CBO использует partition pruning, то
результаты запросов могут давать "неожиданные" результаты:

SQL> select * from emp_part where deptno = 40;

     EMPNO     DEPTNO
---------- ----------
        -1         40

SQL> select * from emp_part where empno = -1;

     EMPNO     DEPTNO
---------- ----------
        -1         40
        -1         40

Наличие глобальных индексов избавляет от проблемы "фантома-дубликата" - в этом случае индекс представляет собой глобальную (
уровня таблицы, а не секции) структуру.

SQL> drop table emp_part
  2  /

Table dropped.

SQL> drop table shadow_emp
  2  /

Table dropped.

SQL> create table emp_part (empno int not null, deptno int not null)
  2  partition by list (deptno) (
  3  partition p_10 values(10),
  4  partition p_20 values(20),
  5  partition p_30 values(30),
  6  partition p_40 values(40)
  7  )
  8  /

Table created.

SQL> create unique index emp_part_u
  2  on emp_part (empno, deptno)
  3  /

Index created.

SQL> alter table emp_part add primary key (empno, deptno)
  2  using index emp_part_u
  3  /

Table altered.

SQL> create table shadow_emp as
  2  select * from emp_part where 1=0
  3  /

Table created.

SQL> insert into shadow_emp (empno, deptno) values(-1, 40)
  2  /

1 row created.

SQL> create unique index shadow_emp_u
  2  on shadow_emp (empno, deptno)
  3  /

Index created.

SQL> alter table shadow_emp add primary key (empno, deptno)
  2  using index shadow_emp_u
  3  /

Table altered.

SQL> alter table emp_part exchange partition p_30
  2  with table shadow_emp 
  3  without validation
  4  update global indexes
  5  /

Table altered.

SQL> select * from emp_part partition (p_30)
  2  /

     EMPNO     DEPTNO
---------- ----------
        -1         40

SQL> insert into emp_part (empno, deptno) values(-1, 40)
  2  /
insert into emp_part (empno, deptno) values(-1, 40)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C009869) violated

Любопытно, что документация на этот счет утверждает, что

без какого-либо упоминания об особенностях, связанных с использованием локальных индексов

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