I am using 11.1.0.7 database
I tried doing the update as a correlated subquery which was taking huge amount of time and then tried merge which is once again slow.
Statistics are upto date. temp table doesn't have any index
claim has a normal btree index on tr_id.
Cost based optimizer(all_rows) is used here.
Both the tables have around 3.5 million rows.
22:34:43 SQL> merge into temp a
22:38:00 2 using claim b
22:38:03 3 on (a.tr_id = b.tr_id)
22:38:11 4 when matched then
22:38:13 5 update set
22:38:15 6 a.tr_sid=b.tr_sid
22:38:22 7 /
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 3394K| 84M| | 94222 (2)| | |
| 1 | MERGE | TEMP | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | HASH JOIN | | 3394K| 1336M| 84M| 94222 (2)| | |
| 4 | TABLE ACCESS FULL | TEMP | 3397K| 45M| | 1740 (8)| | |
| 5 | PARTITION LIST ALL| | 3394K| 1291M| | 31858 (3)| 1 | 2 |
| 6 | TABLE ACCESS FULL| CLAIM | 3394K| 1291M| | 31858 (3)| 1 | 2 |
Any suggestions on what can be done to improve the performance of this query?