Производительность оператора UPDATE

Источник: дт

Эта статья посвящена обсуждению производительности достаточно сложного оператора 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

Болезненное устройство ввода.

Autodesk представляет цифровые 3D-инструкции по сборке LEGO Brickley.

Анатомия атаки: Как я взломал StackOverflow.

Вирус атому не страшен: "Лаборатория Касперского" обеспечила защиту Госкорпорации "Росатом".

Руководителю предприятия: внедрение системы автоматизации, основные проблемы и задачи.

Главная » Игры