Skip to Main Content
  • Questions
  • Joining multiple tables inside a UPDATE statement

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Megs.

Asked: September 21, 2017 - 10:46 am UTC

Last updated: October 06, 2017 - 1:30 am UTC

Version: 10.1.2

Viewed 10K+ times! This question is

You Asked

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.

and Chris said...

¯\_(ツ)_/¯

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!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Megs, September 26, 2017 - 8:26 am UTC

I'm unable to get the execution plan for the update. And i do not have create table statements handy :(
Chris Saxon
October 06, 2017 - 1:30 am UTC

I guess we'll be unable to help you then...

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.