Re: Oracle's update statement

tonixlaw_at_gmail.com wrote:
> Hi all,
>
> I am trying to partially update a table based on a heavy query.
>
> If I use following update:
>
> update
> table_A
> set
> (ColA, ColB) =
> (select ColA, ColB
> from heavy_query_B alias_B
> where id = table_A.id)
> where
> exists
> (select *
> from heavy_query_B alias_B
> where id = table_A.id)
>
> It would be extremely slow. The "where" clause is required to avoid
> updating unwanted rows in table_A, however i think it's one of the
> problems that impact performance.
>
> Any idea that could help?
>
> Thanks a lot!
>

MERGE INTO table_A USING <heavyquery> alias_B

   ON table_A.id = alias_B.id
   WHEN MATCHED THEN UPDATE SET (colA, colB)

• (alias_B.colA, alias_B.colB)

BTW, do you know that the UPDATE is slow? Most vendors optimize updates of that form if <heavy_query> produces distinct id columns.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html