We have a very small Table (84 records) and do an update with a very slow subselect:
UPDATE
SMALL_TABLE T
SET FCR7=
(SELECT
FCR7*100
FROM SLOW_VIEW D
WHERE D.MONAT = T.MONAT
AND D.DL = T.DL
)
;
This Update runs about 3-8 hours.
The Select
SELECT * FROM SLOW_VIEW;
has a result of 63 rows and takes about 7 minutes.
I could use PL/SQL with a loop for the select and inside the update, this should speed up from hours to minutes.
Q: Is there a more elegant way for this update? Optimizer Hint?
DB is Oracle 12c EE
First lines of the Plan, parallel execution disabled:
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 84 | 1764 | | 216M (1)| 10:12:05 | | |
| 1 | UPDATE | SMALL_TABLE | | | | | | | |
| 2 | TABLE ACCESS FULL | SMALL_TABLE | 84 | 1764 | | 3 (0)| 00:00:01 | | |
| 3 | VIEW | SLOW_VIEW | 19047 | 539K| | 2574K (1)| 00:07:18 | | |
| 4 | SORT ORDER BY | | 19047 | 576K| | 2574K (1)| 00:07:18 | | |
Greetings,
Joachim