Эта статья посвящена обсуждению производительности достаточно сложного оператора UPDATE с вложенными. По мотивам очередного ответа Тома Кайта на вопрос, заданный в июне 2003 года.
Как повысить производительность этого оператора UPDATE в версии 8.1.7?
Привет, Том!
Я использую следующий оператор update для таблицы t1:
update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)));
В таблице t1 - около 10000 строк, а в таблице t2 - около 2 миллионов. Я пытаюсь получить в таблице t1 информацию о сумме и дате последней банковской транзакции для всех идентификаторов из таблицы t2 (таблицы транзакций). Таблица t3 (код транзакции) определяет, какие транзакции следует учитывать.
Этот оператор update выполняется достаточно долго. Что бы ты порекомендовал для повышения производительности этого оператора update?
Ответ Тома Кайта
Ну, этот оператор update, при наличии соответствующих индексов и использовании стоимостного оптимизатора, должен работать "сравнительно быстро". На моей персоналке он работает менеее 2 минут - разве это долго?
В любом случае, я сымитировал вашу ситуацию так:
create table t1 ( id int, amount int, enter_date date );
insert into t1
select rownum, object_id, sysdate
from all_objects big_table
where rownum <= 100000;
alter table t1 add constraint t1_pk primary key(id);
analyze table t1 compute statistics
for table
for all indexes
for all indexed columns;
create table t2 ( id int, amount int, enter_date date, code int );
begin
for i in 1 .. 20
loop
insert /*+ APPEND */ into t2
select id, amount-i, enter_date-i, mod(i,2)
from t1;
commit;
end loop;
end;
/
create index t2_idx on t2(id,enter_date);
analyze table t2 compute statistics
for table
for all indexes
for all indexed columns;
create table t3 ( code int, sign int primary key);
insert into t3
select rownum, rownum
from all_users;
analyze table t3 compute statistics
for table
for all indexes
for all indexed columns;
Итак, у меня тоже 100000 строк, 2000000 строк и отдельная таблица для поиска кода транзакции. Я создал следующее представление:
create or replace view v
as
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
/
И попробовал выполнить изменение процедурно:
declare
type ridArray is table of rowid index by binary_integer;
type numArray is table of number index by binary_integer;
type datArray is table of date index by binary_integer;
l_rid ridArray;
l_amt numArray;
l_ed datArray;
l_array_size number := 100;
l_cnt number := 0;
cursor c is select rid, amount, enter_date from v;
begin
open c;
loop
fetch c bulk collect
into l_rid, l_amt, l_ed
limit l_array_size;
forall i in 1 .. l_rid.count
update t1 set amount = l_amt(i), enter_date = l_ed(i)
where rowid = l_rid(i);
l_cnt := l_cnt + l_rid.count;
dbms_application_info.set_client_info( 'updated ' // l_cnt // ' rows' );
exit when c%notfound;
end loop;
close c;
end;
/
Затем попробовал сделать то же самое одним оператором update:
update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)));
Для PL/SQL-блока потребовалось:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.03 0 0 1 0 Execute 1 71.28 80.10 15610 106393 101914 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 71.30 80.14 15610 106393 101915 1
около 80 секунд, а для update:
update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 62.90 108.38 13665 2728542 204504 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 62.90 108.39 13665 2728542 204504 100000
немного больше...
Так что, можете попробовать процедурный подход, но, при наличии соответствующих индексов, изменение 100000 строк не должно вызывать особых проблем.
Почему SQL выполняется медленнее, чем PL/SQL?
Том, что в этом примере делает его исключением из твоего простого правила: один SQL-оператор работает быстрее и использует меньше ресурсов, чем процедурное решение?
Ответ Тома Кайта
Дело в сути изменения: в нем используется много коррелированных подзапросов для поиска max enter_date и т.п. Это усложняет при оптимизации выбор любого другого плана кроме вложенных циклов (именно так выполняются коррелированные подзапросы).
Я бы хотел сделать следующее:
UPDATE(
select *
from ( select t1.amount t1_amt, t1.enter_date t1_ed,
t2.amount t2_amt, t2.enter_date t2_ed,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
)
set t1_amt = t2_amt, t1_ed = t2_ed;
Но, поскольку нет защиты ключом (key preservation) и используются аналитические функции (что подразумевает "упорядочивание" подставляемого представления), так делать нельзя. Я хотел бы выбирать строки с помощью именно такого запроса, поскольку он выполняет множественные операции (что ускоряет работу при большом количестве строк), а не 100000 сложенных циклов...
Итак, чтобы "изменить результаты этого запроса", мне, к сожалению (в 8i), приходится использовать запрос + update. Выполняя множественные операции...
В версии 9i появился оператор MERGE:
merge into t1
using (
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
) t2
on ( t1.rowid = t2.rid )
when matched then
update set t1.amount = t2.amount, t1.enter_date = t2.enter_date
when not matched then
insert (id) values ( null )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 2 43.03 53.45 16486 7558 102809 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 43.05 53.46 16486 7558 102809 100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE (cr=7558 r=16486 w=10807 time=53444549 us)
100000 VIEW (cr=7531 r=16477 w=10807 time=49308344 us)
100000 HASH JOIN OUTER (cr=7531 r=16477 w=10807 time=48646484 us)
100000 VIEW (cr=7187 r=16139 w=10807 time=46776991 us)
1000000 WINDOW SORT (cr=7187 r=16139 w=10807 time=45026596 us)
1000000 HASH JOIN OUTER (cr=7187 r=5331 w=0 time=27551523 us)
100000 INDEX FAST FULL SCAN OBJ#(37458) (cr=215 r=210 w=0 time=235332 us)(object id 37458)
1000000 VIEW (cr=6972 r=5121 w=0 time=18959603 us)
1000000 HASH JOIN (cr=6972 r=5121 w=0 time=15955318 us)
1 TABLE ACCESS BY INDEX ROWID OBJ#(37461) (cr=2 r=0 w=0 time=97 us)
1 INDEX UNIQUE SCAN OBJ#(37462) (cr=1 r=0 w=0 time=58 us)(object id 37462)
2000000 TABLE ACCESS FULL OBJ#(37459) (cr=6970 r=5121 w=0 time=4275638 us)
100000 TABLE ACCESS FULL OBJ#(37457) (cr=344 r=338 w=0 time=376606 us)
Он делает то же самое, что и мой PL/SQL-код, но немного эффективнее.
Аналогичная проблема
Я пытался разобраться, почему мой оператор update выполняется часами, а если разделить его на две части - эти части выполняются несколько секунд. Возможно, проблема связана с самосоединением, но я не уверен.
Моя проблема в том, что первоначально не была учтена возможность рождения однополых двойняшек. В таблице есть столбцы CLIID, пол и дата рождения. Я просто хочу пометить возможные дублирующиеся строки (часть из них может быть связана с двойняшками, а часть - с опечатками при наборе) по каждому CLIID. В таблице - около 150000 записей, из которых около 10000 потенциально могут быть дублирующимися.
Я написал следующий оператор:
update testchild tcx set pd = (select "rank" from (select cliid,id,fname, rank() over (partition by cliid order by fname) "rank" from testchild tc2 where exists (select * from testchild tc3 where tc3.cliid=tc2.cliid and cliid > 1000000000 group by cliid having count(*) > 1)) x where x.id = tcx.id) Plan Table ------------------------------------------------------------------------------------------ / Operation / Name / Rows / Bytes/ Cost / Pstart/ Pstop / ------------------------------------------------------------------------------------------ / UPDATE STATEMENT / / 152K/ 745K/ 284 / / / / UPDATE /TESTCHILD / / / / / / / TABLE ACCESS FULL /TESTCHILD / 152K/ 745K/ 284 / / / / VIEW / / 7K/ 193K/ 319 / / / / WINDOW SORT / / 7K/ 149K/ 319 / / / / FILTER / / / / / / / / TABLE ACCESS FULL /TESTCHILD / 7K/ 149K/ 284 / / / / FILTER / / / / / / / / SORT GROUP BY NOSORT/ / 1 / 9 / 1 / / / / FILTER / / / / / / / / INDEX RANGE SCAN /TC_CLIID / 1 / 9 / 1 / / / ------------------------------------------------------------------------------------------
Сам запрос выполняется около 6 секунд. Если я выполняю CREATE TABLE AS SELECT, а затем изменяю таблицу testchild на основе этой "временной" таблицы, требуется менее минуты. Добавление индекса по ID для временной таблицы особой роли не играет. А вот если выполнять представленный выше полный update... Он начался в 11 утра вчера, и сегодня в 9 утра еще выполняется. Количество consistent gets измеряется миллиардами. Все это работает в версии 9i на персоналке.
Что происходит, не подскажешь?
Ответ Тома Кайта
Проблема та же.
Этот 6-секундный запрос выполняется 152k раз (представленная статистика свидетельсвует, что изменять в таблице придется 152k строк) Представленный ранее прием позволит выполнять 6-секундный запрос блоком (IN BULK), а не построчно, и изменять его результаты.
Прекрасное решение
Я попробовал предложенное процедурное решение, и оно потребовало около 60 секунд. Аналитические функции - это замечательно.
У меня, однако, осталась одна проблема. Вместо создания представления я попытался создать курсор:
CURSOR last_payment_cur IS
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null);
В результате было получено следующее сообщение об ошибке:
LINE/COL ERROR
51/32 PLS-00103: Encountered the symbol "(" when expecting one of the
following: , from
Но этот же запрос в окне SQL*Plus прекрасно работает. Почему нельзя создать курсор?
Ответ Тома Кайта
Я специально создал представление, увидев в вопросе упоминание версии 8.1.7
См. главу в моей книге, посвященную аналитическим функциям. Стр. 586