Oracle update statement using with clause takes for ever

cteoracleupdate

I am trying to write an update statement, which utilizes the result of a series of aggregations of with statement as below.

update final_table
set(destination_col1,destination_col2)=
(
select c4,c5
from
(
  with temp_table1(c1,c2,c3) as
  select c7,c7,c9 from source_table,

  temp_table2(c4,c5,c6) as
  select "some aggregation on c1,c2",c3 from temp_table

  select * from temp_table2
)
where c6= final_table.primary_key
);

This runs forever.

Whereas, when I try to break the same into 2 queries it works.

  1. Creating a table using the inner query.

  2. Use the above-created table to update like,

    update final_table
    set(destination_col1,destination_col2)=
    (select c4,c5 from step1_table where c6=final_table.primary_key)

As suggested here, I tried to check if there are any locks. But I don't have any.

Am I abusing the with statement? Why exactly my expectation is not met?

Best Answer

Am I abusing the with statement?

No, you are abusing the UPDATE statement. With an UPDATE statement, the correlated subquery is executed for each row from final_table.

Updating values from an other table should be typically done with MERGE.

Example:

merge into final_table using
(
select c4,c5,c6
from
(
  with temp_table1(c1,c2,c3) as
  select c7,c7,c9 from source_table,

  temp_table2(c4,c5,c6) as
  select "some aggregation on c1,c2",c3 from temp_table

  select * from temp_table2
)
) inner
on inner.c6 = final_table.primary_key
when matched then update set final_table.destination_col1 = inner.c4, 
                             final_table.destination_col2 = inner.c5;
Related Question