Допустим, что у нас есть таблица, содержащая некие данные о деньгах в количестве 10000 записей, такого вида:
CREATE TABLE MAIN_TABLE
(
ID NUMBER NOT NULL,
SUMMA NUMBER NOT NULL
,CONSTRAINT MAIN_TABLE_PK PRIMARY KEY (ID) ENABLE
);
У этой таблицы есть куча дочерних таблиц и функция расчета некоего остатка денег
CalcRest(id in number,ReportDate in date), собирающая информацию по всем дочерним
таблицам на определенную дату и довольно сложно ее обрабатывающая. Нам нужно получить
отчет содержащий ID, SUMMA и результат CalcRest.
Итерация 0
select id, summa, CalcRest(id,:ReportDate) rest
from main_table;
Предположим, что в таком виде отчет считается 10 часов – все время уходит на запуск
CalcRest для каждой из 10000 записей.
Итерация 1
Добавляем к таблице поле для сохранения последнего значения остатка и триггеры для всех
дочерних таблиц для обновления этого поля:
ALTER TABLE MAIN_TABLE
ADD ("REST" NUMBER DEFAULT 0 NOT NULL);
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
—MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
update main_table
set rest=CalcRest(:new.main_id, trunc(sysdate))
where id=:new.main_id;
end;
Теперь запрос для отчета выглядит так:
select id, summa,
(case when :ReportDate=trunc(sysdate) then rest
else CalcRest(id, :ReportDate) end) rest
from main_table;
Что существенно ускоряет работу отчета, если мы его формируем на текущую дату, для всех остальных дат – 10 часов.
Итерация 2
Добавляем поле с датой изменения остатка и модифицируем триггера:
ALTER TABLE MAIN_TABLE
ADD ("REST_DATE" DATE DEFAULT sysdate NOT NULL);
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
—MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
update main_table
set rest=CalcRest(:new.main_id, trunc(sysdate)),
rest_date=sysdate
where id=:new.main_id;
end;
И запрос:
select id, summa,
(case when :ReportDate=trunc(sysdate) or :ReportDate>rest_date then rest
else CalcRest(id, :ReportDate) end) rest
from main_table;
В результате CalcRest вызывается только для записей у которых остаток менялся после той даты на которую мы формируем отчет.
PS: Получилось немного сумбурно, но данный алгоритм реально работает.
