DB2 v9.1.2 Express-C
Есть четыре таблицы: организации, ее договоры страхования, полисы сотрудников по этим договорам, и собственно сотрудники. Соответственно имеются первичные и внешние ключи. Для каждой таблицы написал триггеры выполняющие элементарные проверки (на допустимость символов и т.п.). В триггерах AFTER UPDATE выполняю запись в отдельные таблицы (аналогичные исходным) истории изменений.

CREATE TRIGGER USRSCHEMA.UPDAF_ORG AFTER UPDATE ON USRSCHEMA.ORGANIZATIONS REFERENCING OLD AS OLDROW NEW AS NEWROW OLD_TABLE AS OLDTABLE NEW_TABLE AS NEWTABLE FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
-- запись в историю --
INSERT INTO USRSCHEMA.ORGANIZATIONS_JN (ххх) VALUES (ххх);
END@

Теперь хочу реализовать каскадное изменение таблиц - к примеру, при изменении статуса организации соответственно меняются записи всех дочерних таблиц. Решил сделать это также в триггере AFTER UPDATE. Делаю так:

CREATE TRIGGER USRSCHEMA.UPDAF_ORG AFTER UPDATE ON USRSCHEMA.ORGANIZATIONS REFERENCING OLD AS OLDROW NEW AS NEWROW OLD_TABLE AS OLDTABLE NEW_TABLE AS NEWTABLE FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF NEWROW.ORG_STA_ID=-1 THEN
UPDATE USRSCHEMA.CONTRACTS SET CON_STA_ID=-1, CON_OPR_ID=NEWROW.ORG_OPR_ID
WHERE CON_ORG_ID=OLDROW.ORG_ID;
DELETE FROM USRSCHEMA.CONTRACTS WHERE CON_STA_ID=-1;
ELSE
IF NEWROW.ORG_STA_ID=0 THEN
UPDATE USRSCHEMA.CONTRACTS SET СON_STA_ID=0, CON_OPR_ID=NEWROW.ORG_OPR_ID WHERE CON_ORG_ID=OLDROW.ORG_ID;
END IF;
END IF;
-- запись в историю --
INSERT INTO USRSCHEMA.ORGANIZATIONS_JN (ххх)
VALUES (ххх);
END@

Для других таблиц триггеры аналогичные. При этом получаю жуткие тормоза, БД требует под компиляцию запроса UPDATE ORGANIZATIONS SET ORG_STA_ID=-1 WHERE ORG_ID=1 море памяти, план запроса показывает что-то невообразимое - тьма FILTER и ТABLE SCAN (индексы по полям участвующим в кляузе WHERE имеются). Если же реализацию логики перенести
в ХП - то все работает без проблем. Но хотелось бы это сделать именно триггером - полагаю так более правильно.
Помогите понять логику оптимизатора, что я тут принципиально не так делаю. Возможно есть другой способ?

Mark Barinstein,

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

В результате экспериментов вынес операции UPDATE и DELETE в ХП (практически копи/паст) и вызываю их из триггера. Стало работать в разы быстрее (по ощущениям - примерно на порядок). Но не вполне понятно почему. Т.к. условия в WHERE не менялись, сами операции вставки-удаления тоже по-сути те же, то не похоже, что проблема в отсутствии индексов/статистики или в конфигурации БД и т.п.. Думаю, что - в особенностях построения плана оптимизатором или непосредственно в выполнении. А т.к. производительность отличается весьма существенно, то разница должна быть принципиальной. Вот это я и хочу понять - в чем собственно?

Mark Barinstein,

Попробовал разные варианты (Ваш в т.ч.) на тестовых таблицах (три десятка записей в таблице сотрудников). Получил следующее:
1. Вариант - исходный, только изменил DELETE FROM USRSCHEMA.CONTRACTS WHERE CON_STA_ID=-1 на DELETE FROM USRSCHEMA.CONTRACTS WHERE CON_STA_ID=NEWROW.ORG_ID (первичный ключ из ORGANIZATIONS)
Результат: 660 попугаев затрат, время выполнения UPDATE (примерное) - 19 сек.

2. Ваш вариант - 492 попугая затрат, время выполнения UPDATE (примерное) - 19 сек.

3. Вариант с вызовом из триггера ХП (в которой UPDATE и DELETE) - 53 попугая затрат, время выполнения UPDATE (примерное) - 2 сек.

Решил пока остановиться на третьем варианте (хотя не скажу, что он мне нравится) но нет уверенности что при росте объема данных производительность резко не упадет.

gardenman,

Планы выполнения для вариантов 1 и 2 схожи - масса объединений, GENROW, FILTER и TBSCAN (всего узлов в плане около 7-8 десятков, IXSCAN присутвует ближе к вершине дерева). Для варианта 3 - план значительно проще (полагаю, из-за наличия уже построенных планов ХП). Начинаю подозревать что именно поэтому (использование уже построенных ранее планов "как есть") база работает быстрее - много затрат уходит на построение плана для триггера (он строится при каждом вызове или все-таки нет?).