Base Table1
Transaction Table
I want to update my transaction table Brcode based on my base table1. Can you please help me on this.
update trans tl
set brcode = (
select brcode
from basetbl tlb
where tl.accountno=tlb.accountno
);
This query taking long run.
Final output
This query essentially executes subselect as much times as there are rows in your trans table. The better approach is to use MEGRE statement.
Compare the plans, column starts contains the number of executions.
Index on basetbl.accountno will improve the performance, but hash join looks better for this task since the large number of rows in trans table.
| PLAN_TABLE_OUTPUT |
| :--------------------------------------------------------------------------------------- |
| EXPLAINED SQL STATEMENT: |
| ------------------------ |
| update /*+ GATHER_PLAN_STATISTICS*/ trans tl set brcode = ( select |
| brcode from basetbl tlb where tl.accountno=tlb.accountno ) |
| |
| Plan hash value: 2300756728 |
| |
| ---------------------------------------------------------------------------------------- |
| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | |
| ---------------------------------------------------------------------------------------- |
| | 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 403 | |
| | 1 | UPDATE | TRANS | 1 | | 0 |00:00:00.01 | 403 | |
| | 2 | TABLE ACCESS FULL| TRANS | 1 | 99 | 99 |00:00:00.01 | 3 | |
| |* 3 | TABLE ACCESS FULL| BASETBL | 99 | 1 | 9 |00:00:00.01 | 297 | |
| ---------------------------------------------------------------------------------------- |
| |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| |
| 3 - filter("TLB"."ACCOUNTNO"=:B1) |
| |
| Note |
| ----- |
| - dynamic sampling used for this statement (level=2) |
| |
| PLAN_TABLE_OUTPUT |
| :----------------------------------------------------------------------------------------- |
| EXPLAINED SQL STATEMENT: |
| ------------------------ |
| merge /*+ GATHER_PLAN_STATISTICS*/ into trans t using basetbl s on ( |
| t.accountno = s.accountno ) when matched then update set brcode = |
| s.brcode |
| |
| Plan hash value: 4181434565 |
| |
| ------------------------------------------------------------------------------------------ |
| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | |
| ------------------------------------------------------------------------------------------ |
| | 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.01 | 15 | |
| | 1 | MERGE | TRANS | 1 | | 0 |00:00:00.01 | 15 | |
| | 2 | VIEW | | 1 | | 9 |00:00:00.01 | 6 | |
| |* 3 | HASH JOIN | | 1 | 9 | 9 |00:00:00.01 | 6 | |
| | 4 | TABLE ACCESS FULL| BASETBL | 1 | 9 | 9 |00:00:00.01 | 3 | |
| | 5 | TABLE ACCESS FULL| TRANS | 1 | 99 | 99 |00:00:00.01 | 3 | |
| ------------------------------------------------------------------------------------------ |
| |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| |
| 3 - access("T"."ACCOUNTNO"="S"."ACCOUNTNO") |
| |
| Note |
| ----- |
| - dynamic sampling used for this statement (level=2) |
| |
db<>fiddle here
basetbl(accountno, brcode).