Hello Tom,
Need your feedback on joining multiple tables inside UPDATE statement
Scenario: i want to update a column value of table 'a' based on the reference/key value in table 'd'. Below is my query. It's working good for small number of records. But when it comes to lacs of records, query just halts for long time. Is there any other way of writing this query.
update
(select a.category as defaultcategory from a
left join b on b.xx_id = a.xx_id
left join c on c.zz_id = b.zz_id
left join d on d.yy_id = c.yy_id
where d.yy_id = ‘123’) dc
set dc.defaultcategory = (select kk_id from cc where name = 'NNN')
Thanks in Advance.
¯\_(ツ)_/¯
There probably is. But we need more details!
Specifically, please include:
- create table statements for all your tables. Ensure this includes any constraints & indexes on these tables!
- The execution plan for the update
You can get this by adding the gather_plan_statistics hint to your query. Then calling DBMS_Xplan.display_cursor. Like so:
set serveroutput off
update /*+ gather_plan_statistics */t
set x = x + 1;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
SQL_ID g4ya70274azsu, child number 0
-------------------------------------
update /*+ gather_plan_statistics */t set x = x + 1
Plan hash value: 4095874790
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 10 |
| 1 | UPDATE | T | 1 | | 0 |00:00:00.01 | 10 |
| 2 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------- Ensure the plan you send us includes those E-rows, A-rows and buffers columns.
One observation:
All your joins are outer joins. But you have a where clause on D. So these are inner joins. Make them so!