Вопрос. У меня есть родительская и дочерняя таблицы. Дочерняя таблица имеет столбец 'qty' (количество). Я хочу просуммировать его и сохранить результат в столбце родительской таблицы 'tot_qty' (общее количество). Любые изменения (операторы UPDATE, DELETE, INSERT) дочернего столбца 'qty' должны вносится в родительский столбец 'tot_qty'. Я знаю, что это можно сделать с помощью триггеров, но не знаю, как приступить к этому.
Ответ. Суммирование столбцов QTY для родительской записи выполняется чертовски быстро. Но прежде чем вы сделаете это, убедитесь, что вы решаете реальную проблему. СУБД родились, чтобы соединять и агрегировать данные; это – то, что они делают наилучшим образом. Кроме того, помните, с этим подходом к суммированию, вы будете сериализовать транзакции на уровне родительского записи. Каждая модификация дочерней записи будет сериализовать все транзакции на родительском уровне.
Есть два подхода. Вы можете воспользоваться подходом "сделай сам", используя триггеры, или же можете использовать материализованное представление с предложением ON COMMIT REFRESH (обновление при фиксации транзакции). Во втором варианте родительская таблица не будет иметь столбца итогов, но вы будете иметь таблицу итогов с родительским ключом и подсчетом итогов.
Сначала, для подхода "сделай сам", я предполагаю следующую структуру таблиц:
create table p ( x int primary key, qty number ); insert into p (x) values (1); create table c ( a int primary key, x references p, qty number not null );Это – родительская (P) и дочерняя (C) таблицы, и я хочу поддерживать сумму по столбцу C.QTY в столбце P.QTY. Триггер, который я использовал бы, будет похож на следующее:
create or replace trigger c_trigger
after insert or delete or update
of qty, x on C
for each row
begin
if ( inserting or updating )
then
update p
set qty = nvl(qty,0)+:new.qty
where x = :new.x;
end if;
if ( updating or deleting )
then
update p
set qty = nvl(qty,0)-:old.qty
where x = :old.x;
end if;
end;
/
Этот триггер срабатывает каждый раз, когда я модифицирую столбец QTY в таблице C или изменяю значение внешнего ключа в таблице C (перемещая, следовательно, количество из одной родительской записи в другую). Когда я выполняю операцию вставки, я только добавляю к записи QTY родительской таблицы (используя функцию NVL, чтобы сначала преобразовать какие-либо NULL-значения в нули). Когда я выполняю операцию удаления, я только вычитаю. Когда я выполняю операцию обновления, я добавляю новое значение к новой записи внешнего ключа и вычитаю старое значение из старой записи внешнего ключа.Теперь, создав триггер, я могу выполнять операции вставки и обновления, такие, как:
insert into c(a,x,qty) values ( 100, 1, 55 ); 1 row created. update c set qty = qty+100; 1 row updated.
И я могу проверить модификации родительской записи:
select * from p;
X QTY
-------- --------
1 155
Я говорил, что есть другой способ (не использующий триггеры "сделай сам"), это –материализованные представления. (Подробно о них см. в руководстве по хранилищам данных Oracle Data Warehousing Guide.) Эквивалентное решение (предполагаем, что вы только что создали таблицы P и C и здесь нет никаких триггеров) может быть следующим:create materialized view log
on c with rowid(x,qty)
including new values;
Materialized view log created.
create materialized view mv
refresh fast on commit
as
select c.x,
sum(qty) sum_qty,
count(*) cnt,
count(qty) cnt_qty
from c
group by c.x
/
Materialized view created.
Больше ничего нет. Таблица MV (материализованное представление создает и поддерживает физическую таблицу) содержит просуммированное значение QTY, а так же значения функций COUNT(*)и COUNT(QTY) – необходимое условие создания материализованного представления агрегатов одной таблицы с быстрым обновлением ("single table aggregate fast refresh materialized view") – без них невозможно его инкрементое обновление.
Оператор MERGE – что и когда?
Вопрос. Я озадачен оператором MERGE и срабатыванием триггеров базы данных. Какие триггеры будут срабатывать при выполнении оператора MERGE?
Ответ. Оператор MERGE – забавный оператор – он может быть и оператором вставки данных INSERT, и оператором обновления UPDATE, а в сервере Oracle Database 10g он может быть и оператором удаления DELETE. Следовательно, когда вы выполняете оператор MERGE, в сервере Oracle9i Database немедленно сработают триггеры BEFORE UPDATE и BEFORE INSERT (так как в этом операторе предложения WHEN MATCHED THEN UPDATE (когда совпадают, то обновить) И WHEN NOT MATCHED THEN INSERT (когда не совпадают, то вставить) являются обязательными). В сервере Oracle Database 10g будут срабатывать триггеры BEFORE UPDATE, INSERT и/или DELETE – в зависимости от указанных в операторе MERGE операций.
Подобным образом после выполнения оператора MERGE будут срабатывать и триггеры AFTER. В сервере Oracle9i Database будут срабатывать любые триггеры AFTER UPDATE или AFTER INSERT, а в сервере Oracle Database 10g – любые триггеры AFTER.
Триггеры строк для операторов INSERT и UPDATE (а в сервере Oracle Database 10g и DELETE) будут срабатывать по мере выполнения оператором MERGE соответствующих операций.
Таблица, которую я буду использовать для демонстрации этого, очень простая:
create table t (x varchar2(1), y varchar2(1) );
Для этой таблицы я создам шесть триггеров, используя следующий шаблон:
create or replace trigger t_bi
before insert on t
begin
dbms_output.put_line
('before insert');
end;
/
То же самое я сделаю и для оставшихся пяти триггеров BEFORE UPDATE, BEFORE DELETE и AFTER UPDATE/INSERT/DELETE. Теперь я выполню:
merge into t
using dual
on (dummy=x)
when matched
then update
set y = nvl(y,0)+1
when not matched
then insert (x,y)
values (dummy,null);
before insert
before update
after update
after insert
1 row merged.
И именно это вы увидели бы как в сервере Oracle9i Database, так и в сервере Oracle Database 10g – будут срабатывать все триггеры BEFORE INSERT/UPDATE и AFTER UPDATE/INSERT, даже при том, что в этом случае единственным выполненным действием была вставка данных (никаких совпадений не было, поскольку таблица T была пустой). Это и предполагалось, так как обновление пустой таблицы должно было инициировать срабатывание соответствующих триггеров BEFORE и AFTER. Вставка нуля строк также должна была инициировать срабатывание соответствующих триггеров. Так как при выполнении оператора MERGE могли быть выполнены операторы INSERT и UPDATE, сработать должны были все эти пары триггеров.В сервере Oracle Database 10g каждая часть оператора MERGE является факультативной – мы не обязаны указывать опцию вставки, мы не обязаны указывать опцию обновления и у нас также есть опция удаления данных. Я могу иметь такой оператор MERGE:
merge into t
using dual
on (dummy=x)
when matched
then update
set y = nvl(y,0)+1
delete where y = 2
when not matched
then insert (x,y)
values (dummy,null);
before insert
before update
before delete
after update
after insert
after delete
1 row merged.
Обратите внимание, что теперь сработали все шесть триггеров, поскольку могли иметь место любые из операций INSERT, UPDATE или DELETE. Если я опущу необязательное (в сервере Oracle Database 10g) предложение INSERT:
merge into t
using dual
on (dummy=x)
when matched
then update
set y = nvl(y,0)+1
delete where y = 2;
before update
before delete
after update
after delete
1 row merged.
Теперь триггеры INSERT не срабатывают. В сервере Oracle Database 10g будут срабатывать только те триггеры которые могли бы сработать. Первоначальные вопросы и ответы на эту тему см. в asktom.oracle.com/~tkyte/merge_trigger.html; в этом интерактивном обсуждении имеется больше примеров, а также рассматривается срабатывание триггеров на уровне строк.Представления и слияние /проталкивание
Вопрос. Я разбираюсь с руководством по оптимизации производительности Oracle9i Performance Tuning Guide and Reference. Я прочитал о слиянии представлений (view merging) и проталкивании предикатов (predicate pushing). Не могли бы вы растолковать их?
Ответ. Слияние представления просто означает переписывание запроса без использования представления. То есть, вместо:
select * from ПРЕДСТАВЛЕНИЕ;На самом деле вводится текст ПРЕДСТАВЛЕНИЯ.
Проталкивание предикатов происходит, когда предикат перемещается вниз по плану выполнения, насколько это возможно (и вычисляется как можно скорее). Я буду для демонстрации использовать три представления:
- несливаемое, "непроталкиваемое" представление;
- сливаемое представление;
- несливаемое представление, которое позволяет проталкивать предикаты.
Таблицы, которые я буду использовать, очень просты:
create table t1 as select * from all_users; create table t2 as select * from all_users;
Я создаю первое представление следующим образом:
create or replace view v1
as
select t1.user_id, t2.username,
rownum r
from t1 inner join t2
on ( t1.user_id =
t2.user_id );
Я не смогу слить это представление и не смогу протолкнуть предикаты. Причина в этом случае заключается в номерах строк ROWNUM (обычно то же самое влияние оказывают и аналитические функции). Причина в том, что значение R изменилось бы, если бы я применил предикаты перед присваиванием R значения ROWNUM. Результат выполнения оператора SELECT * FROM (SELECT ..., ROWNUM R FROM T) WHERE <предикат> существенно отличается от результата выполнения оператора
SELECT ..., ROWNUM R FROM T WHERE <предикат>.
Я создаю второе представление следующим образом:
create or replace view v2
as
select t1.user_id, t2.username
from t1 inner join t2
on ( t1.user_id =
t2.user_id );
Это представление может быть слито. То есть, сервер Oracle Database будет в состоянии переписать оператор SELECT * FROM V2 с заменой V2 в самом запросе на текст этого представления. Таким образом, оптимизатор имеет лучший шанс выработать хороший полный план выполнения.
Я создаю последнее представление следующим образом:
create or replace view v3
as
select t1.user_id, t2.username
from t1 inner join t2
on ( t1.user_id =
t2.user_id )
order by t1.user_id,
t2.username;
Предложение ORDER BY в этом представлении не допускает общего слияния, но допускает проталкивание предиката (в отличие от примера с ROWNUM в представлении V1). Итак, как мы можем реально увидеть эти факты? Ответ: воспользуемся оператором EXPLAIN PLAN. Давайте посмотрим на запросы к каждому из наших представлений и проанализируем результаты. На листинге 1 показан запрос к представлению V1.
SQL> explain plan for
2 select * from v1 where username = 'FRED';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | VIEW | V1 | | | |
| 2 | COUNT | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL | T2 | | | |
|* 6 | SORT JOIN | | | | |
| 7 | TABLE ACCESS FULL | T1 | | | |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------
1 - filter("V1"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Появление на листинге 1 шага VIEW (Id=1) указывает, что это представление не было слито с самим запросом. Факт, что фильтр WHERE USERNAME='FRED' появился поздно (Id=6), показывает, что предикат не был протолкнут в представление. Сервер Oracle Database будет материализовать представление, а затем применит предикат. Это – пример представления, которое не может быть слито и не поддерживает проталкивание предиката.Второй пример, запрос к представлению V2, показан на листинге 2.
SQL> create or replace trigger no_more_than_10
SQL> explain plan for
2 select * from v2 where username = 'FRED';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | MERGE JOIN | | | | |
| 2 | SORT JOIN | | | | |
|* 3 | TABLE ACCESS FULL | T2 | | | |
|* 4 | SORT JOIN | | | | |
| 5 | TABLE ACCESS FULL | T1 | | | |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
3 - filter("T2"."USERNAME"='FRED')
4 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
Отсутствие на листинге 2 шага VIEW указывает, что представление было слито. Это как если бы я запросил:select t1.user_id, t2.username
from t1 inner join t2
on ( t1.user_id =
t2.user_id )
where t2.username = 'FRED';
Вместо:
select * from v2 where username = 'FRED';
Сервер Oracle Database просто переместил наш текст непосредственно в сам запрос и оптимизировал его, как будто представление даже не существует.
Последний запрос, к представлению V3, показан на листинге 3.
SQL> explain plan for
2 select * from v3 where username = 'FRED';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | V3 | | | |
| 2 | SORT ORDER BY | | | | |
| 3 | MERGE JOIN | | | | |
| 4 | SORT JOIN | | | | |
|* 5 | TABLE ACCESS FULL | T2 | | | |
|* 6 | SORT JOIN | | | | |
| 7 | TABLE ACCESS FULL | T1 | | | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------
5 - filter("T2"."USERNAME"='FRED')
6 - access("T1"."USER_ID"="T2"."USER_ID")
filter("T1"."USER_ID"="T2"."USER_ID")
На листинге 3 я вижу шаг VIEW, указывающий, что представление не было слито. Я запросил упорядочение строк, поэтому я не мог слить этот шаг. Предикаты применяются к представлению, однако они были протолкнуты в запросе настолько далеко, насколько это возможно. Шаг 5 (Id=5) на плане листинга 3 показывает, что предикат WHERE USERNAME = 'FRED' применяется так рано, как это можно (так как он не будет модифицировать ответ, возвращаемый запросом, – в отличие от запроса первого представления, который я анализировал (листинг 1)).Перемещение таблицы
Вопрос. Из-за реорганизации одного из моих файлов данных мне нужно перенести одну таблицу в другой файл данных. Я думал о двух возможностях. Одна – создать другое табличное пространство, экспортировать таблицу, удалить ее, предварительно создать таблицу в другом табличном пространстве и импортировать ее. Другая – для перемещения таблицы в другой файл данных использовать операцию ALTER TABLE MOVE.
Какой вариант лучше в терминах времени недоступности таблицы, потребления пространства и возобновляемости процесса? Тестовой системы у меня нет. Таблица – традиционная таблица размером приблизительно 3.5 ГБ с одним первичным ключом.
Ответ. Есть только два подхода, которые я мог бы рассмотреть:
Во-первых, если было бы можно использовать время простоя, проще всего воспользоваться оператором ALTER TABLE T MOVE ТАБЛИЧНОЕ_ПРОСТРАНСТВО НОВОЕ_ ТАБЛИЧНОЕ_ПРОСТРАНСТВО. Это можно сделать, если хотите, в режиме отказа от журнализации NOLOGGING (но, если вы работаете в режиме архивирования журнальных файлов, не забудьте немедленно после переноса таблицы создать резервную копию). Во время переноса запрещена модификация этой таблицы, а затем потребуется перестройка индексов. Во время переноса можно выполнять запросы, но сразу же после переноса все индексы станут неиспользуемыми (unusable), поэтому запросы начнут сбиваться до тех пор, пока вы не перестроите эти индексы. Этот подход является полностью транзакционным – в противоположность использованию утилит EXP и IMP (всегда, когда вы забираете данные из базы данных, я нервничаю, потому что вы можете потерять их). Оператор ALTER TABLE MOVE либо выполняется успешно, либо оставляет данные такими, какими они были.
Второй подход заключается в использовании пакета DBMS_REDEFINITION, доступного в сервере Oracle9i Database и более поздние версии; поищите DBMS_REDEFINITION на сайте asktom.oracle.com для получения быстрых примеров. Преимущества этого пакета состоят в том, что он поддерживает выполнение непрерывных запросов и модификаций. Однако в сервере Oracle9i Database вы несете ответственность за то, что новый объект удовлетворяет всем вашим потребностям (таким, как наличие индексов, ограничений целостности, триггеров и т.д.). Сервер Oracle Database 10g берет все это на себя – сервер базы данных может выполнить все служебные операции, обеспечивая наличие у переопределенной таблицы всех необходимых предоставлений привилегий, индексов, ограничений, триггеров и т.д.; таким образом он делает ее прекрасной копией оригинала. Этот процесс также является транзакционным – никакие данные не могут пропасть.
Для таблицы размером 3.5 ГБ любой подход, невзирая ни на что, будет довольно быстрым; в наши дни 3.5 ГБ – это не очень много. Если вы можете допустить время простоя, то использование оператора ALTER TABLE MOVE – вероятно, самый легкий подход (не забудьте перестраивать индексы!). Но если время простоя не допускается, использование пакета DBMS_REDEFINITION – это то, что надо.
Источник.
Комментариев нет:
Отправить комментарий