EnUPDATE данными из других таблиц
KDV, www.ibase.ru
Иногда возникает необходимость глобально обновить столбец одной таблицы значениями из этой же таблицы или из другой таблицы. Обычно для таких целей используют запрос вида:
update TABLE1
set FIELD1 = (select PRIMARYKEY
from TABLE1 T1
where T1.FIELD1=FIELD2)
set FIELD1 = (select PRIMARYKEY
from TABLE1 T1
where T1.FIELD1=FIELD2)
Это единственный способ обновить таблицу таким образом, поскольку синтаксис "update TABLE1, TABLE2..." в IB не поддерживается.
Вероятность, что вышеприведенный запрос будет работать медленно, весьма высока. Намного выше чем у запроса, обновляющего записи данными из другой таблицы. Посмотрите план такого запроса, и если увидите там два цикла с перебором записей NATURAL, то запрос надо менять. Ускорить его можно путем "переворачивания" update и select местами внутри хранимой процедуры.
...
for select T1.PK, T2.PK
from TABLE1 T1, TABLE1 T2
where T1.FIELD1 = T2.FIELD1
into :TARGET, :SOURCE
do
begin
update TABLE1
set FIELD1 = :SOURCE
where PK = :TARGET;
end
for select T1.PK, T2.PK
from TABLE1 T1, TABLE1 T2
where T1.FIELD1 = T2.FIELD1
into :TARGET, :SOURCE
do
begin
update TABLE1
set FIELD1 = :SOURCE
where PK = :TARGET;
end
Такая конструкция осуществит обновление всего за один "проход" по записям TABLE1, но в любом случае стоит проверить план оператора select и план оператора update отдельно.
Есть еще один способ, который аналогичен приведенному for select, но использует номер записи IB – RDB$DB_KEY:
create procedure TESTUPD
as
declare variable db_key CHAR(8);
begin
for select RDB$DB_KEY, ...
from TAB
into :db_key
do
update TAB
set ...
where RDB$DB_KEY = :db_key;
end
as
declare variable db_key CHAR(8);
begin
for select RDB$DB_KEY, ...
from TAB
into :db_key
do
update TAB
set ...
where RDB$DB_KEY = :db_key;
end
Причем по таблице TAB может не быть индекса совсем, но по скорости выполнения такая конструкция практически равна скорости с оптимизацией по индексам. Например, если таблица TAB не имеет ни одного индекса, то без rdb$db_key время обновления 3-х тысяч записей 1500 секунд, а с rdb$db_key – 10 секунд.
Замечание. Обратите внимание, что длина db_key равна 8 байт. Если таблица TAB на самом деле является view, состоящим из двух таблиц, то длина db_key должна быть 16 байт, и так далее.
Неожиданный способ на основе ключевых слов AS CURSOR и WHERE CURRENT OF:
declare variable counter integer;
declare variable x integer;
begin
counter = 1;
for select c1 from t2 into :x
as cursor FOO
do
begin
update t2
set c2 = c1 / :counter, c1 = :counter
where current of foo;
counter = :counter + 1;
end
end
declare variable x integer;
begin
counter = 1;
for select c1 from t2 into :x
as cursor FOO
do
begin
update t2
set c2 = c1 / :counter, c1 = :counter
where current of foo;
counter = :counter + 1;
end
end
Здесь таблица T2 рассматривается как курсор FOO, что в принципе эквивалентно предыдущему примеру с RDB$DB_KEY. Переменные counter, x, c2 и c1 – просто пример применения.
Выгода этого способа перед предыдущим – отсутствие необходимости объявлять переменную для хранения RDB$DB_KEY. Как упоминалось выше, размер db_key для view зависит от количества таблиц, на котором это view построено. При использовании as cursor и current of не нужно задумываться, какой объект используется для сканирования и обновления – таблица или view.