Продуктивність оператора 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. У таблиці – близько 150 000 записів, з яких близько 10 000 потенційно можуть бути дублюються.


Я написав наступний оператор:

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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*