При оптимизации древней процедуры столкнулся с аналогичной ситуацией.
Была такая конструкция, выполнялась за ~9 минут
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
open cur(var1, var2, var3);
loop
fetch cur
into rfc /*cur%rowtype*/;
exit when cur%notfound;
update tbl
set field1 = rfc.field1
where field2 = var1
and field3 = rfc.field3;
end loop;
close cur;
Попробовал переписать на такую, выполняется за ~14 секунд
1.
2.
3.
4.
5.
6.
7.
for cur in (select * from dual /*для примера*/)
loop
update tbl
set field1 = cur.field1
where field2 = var1
and field3 = cur.field3;
end loop;
план запроса
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1486 | 89160 | 10908 | 00:00:01 |
| 1 | WINDOW BUFFER | | 1486 | 89160 | 10908 | 00:00:01 |
| 2 | HASH GROUP BY | | 1486 | 89160 | | |
| 3 | CONCATENATION | | | | | |
| * 4 | FILTER | | | | | |
| * 5 | HASH JOIN | | 11405 | 684300 | 5906 | 00:00:01 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 11421 | 605313 | 4572 | 00:00:01 |
| * 8 | TABLE ACCESS BY INDEX ROWID | INFLUENCEM | 202 | 2828 | 25 | 00:00:01 |
| * 9 | INDEX RANGE SCAN | INFLUENCEM_IDX | 249 | | 2 | 00:00:01 |
| 10 | PARTITION RANGE SINGLE | | 3 | | 1 | 00:00:01 |
| * 11 | INDEX RANGE SCAN | INV_APT_ID_MP_DT_IDX | 3 | | 1 | 00:00:01 |
| 12 | TABLE ACCESS BY LOCAL INDEX ROWID | TABLE_INV_APT | 57 | 2223 | 23 | 00:00:01 |
| * 13 | TABLE ACCESS FULL | MIT_PROD | 167870 | 1175090 | 1332 | 00:00:01 |
| * 14 | FILTER | | | | | |
| 15 | NESTED LOOPS | | | | | |
| 16 | NESTED LOOPS | | 12127 | 727620 | 5000 | 00:00:01 |
| 17 | NESTED LOOPS | | 212 | 4452 | 227 | 00:00:01 |
| * 18 | TABLE ACCESS BY INDEX ROWID | INFLUENCEM | 202 | 2828 | 25 | 00:00:01 |
| * 19 | INDEX RANGE SCAN | INFLUENCEM_IDX | 249 | | 2 | 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | MIT_PROD | 1 | 7 | 1 | 00:00:01 |
| * 21 | INDEX RANGE SCAN | MIT_PROD_ID_HMP | 1 | | 1 | 00:00:01 |
| 22 | PARTITION RANGE SINGLE | | 3 | | 1 | 00:00:01 |
| * 23 | INDEX RANGE SCAN | INV_APT_ID_MP_DT_IDX | 3 | | 1 | 00:00:01 |
| * 24 | TABLE ACCESS BY LOCAL INDEX ROWID | TABLE_INV_APT | 57 | 2223 | 23 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Сам запрос для обеих конструкций одинаков и выполняется ~за 4 сек. Возвращает он 1000 строк (6 полей, все числовые). Версия Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
В интернетах так и не нашел описания для подобных тормозов.
В чем причина столь явного разрыва в производительности?
Где можно почитать про это?
