Hi All,
Hope everyone is safe in these times.
I basic question, but still want to give it a try if we can improve the performance of update.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for IBM/AIX RISC System/6000: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
optimizer_features_enable --> 11.2.0.4 (this was done intentionally as our testing was done partially and DBA recommended this).
We have a huge table: transactions (monthly partitioned and primary index on transaction id). This table has data all the way from 2002.
We have a situation where we need to update many records in this table for every year based on some input tables which has data based on transaction ID (indexed column in temporary table).
I am using a direct update statement :
[code]
UPDATE /*+ parallel(32 ) */ transaction t
SET
( col1,
col2,
col3 ) = (
SELECT
col1,col2,col3
FROM
table_for_update i
WHERE
i.transaction_id = t.transaction_id
)
WHERE
t.transaction_id IN (
SELECT
transaction_id
FROM
table_for_update i
)
[/code]
This works fine sometimes and for records greater than 1 M , it is running slow. Explain plan for update shows a nested loop join just before update.
Cost for loop is around 3.7 M and cost for update is 448M.
Is there any alternate which i can try to speed up the update. There is a trigger which i am disabling before the update.
Also since this is a core table so we cannot disable check constraints and foreign key constraints.
Any suggestions welcome. DO let me know if further information is required.
Thanks,
Nik