dear tom, i am using a query that update a column of one table. But the query is very costly and taking huge time to complete. though i am using it parallel.
The query is:
UPDATE LOANACNTS l set l.LNACNT_RTMP_LAST_DATE=(SELECT /*+parallel (32) */ MAX (ll.LOANIAMRR_VALUE_DATE)
FROM LOANIAMRR ll
WHERE LOANIAMRR_ENTITY_NUM = 1
AND ll.LOANIAMRR_ACNT_NUM=l.LNACNT_INTERNAL_ACNUM
GROUP BY ll.LOANIAMRR_ACNT_NUM)
WHERE l.LNACNT_ENTITY_NUM=1;
Plan:
UPDATE STATEMENT ALL_ROWS
Cost: 2,997,377,100 Bytes: 20,317,920 Cardinality: 967,520
10 UPDATE SBLP.LOANACNTS
4 PX COORDINATOR
3 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ20000 :Q2000
Cost: 140 Bytes: 20,317,920 Cardinality: 967,520
2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q2000
Cost: 140 Bytes: 20,317,920 Cardinality: 967,520
1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT SBLP.LOANACNTS :Q2000
Cost: 140 Bytes: 20,317,920 Cardinality: 967,520
9 SORT GROUP BY Cost: 3,098 Bytes: 20 Cardinality: 1
8 PX COORDINATOR
7 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000
Cost: 3,098 Bytes: 3,220 Cardinality: 161
6 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000
Cost: 3,098 Bytes: 3,220 Cardinality: 161
5 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT SBLP.LOANIAMRR :Q1000
Cost: 3,098 Bytes: 3,220 Cardinality: 161
Now how can i optimize this query???
UPDATE LOANACNTS l
SET l.LNACNT_RTMP_LAST_DATE=
(SELECT MAX (ll.LOANIAMRR_VALUE_DATE)
FROM LOANIAMRR ll
WHERE LOANIAMRR_ENTITY_NUM = 1
AND ll.LOANIAMRR_ACNT_NUM = l.LNACNT_INTERNAL_ACNUM
GROUP BY ll.LOANIAMRR_ACNT_NUM
)
WHERE l.LNACNT_ENTITY_NUM=1;
The key here is the expense of the running that inner subquery for each row that satisfies the base predicate (WHERE l.LNACNT_ENTITY_NUM=1). If there's thousands (or millions of rows) then that can quickly add up.
So you need to make that inner query more efficient or run it less times.
a) more efficient
Consider an index on LOANIAMRR_ENTITY_NUM,LOANIAMRR_ACNT_NUM,LOANIAMRR_VALUE_DATE
b) run it less times
Consider instantiating the result of
SELECT LOANIAMRR_ACNT_NUM, MAX (ll.LOANIAMRR_VALUE_DATE)
FROM LOANIAMRR ll
WHERE LOANIAMRR_ENTITY_NUM = 1
GROUP BY ll.LOANIAMRR_ACNT_NUM
which is one pass through the table then use that either as direct inline update, or save the results into a GTT and use that.