Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: need help with query
Lisa M. Lewis wrote:
>
> Can anyone tell me if there is a better way to perform this update:
>
> UPDATE users u
> SET u.usermapid = ( SELECT ncm.usermapid
> FROM NewCookieMap ncm
> WHERE ncm.cookie = u.cookie )
> WHERE EXISTS ( SELECT 'x'
> FROM NewCookieMap ncm
> WHERE ncm.cookie = u.cookie );
>
> This query is in intended to update several million records.
>
> snip
Firstly, ensure that there is an index on NewCookieMap (cookie [,...])!
Secondly, if the subquery predicate is selective on USERS (i.e. identifies a relatively small subset of rows to be updated) AND the table NewCookieMap is small compared to USERS, replace the EXISTS () subquery with:
WHERE u.cookie in
(SELECT ncm.cookie from NewCookieMap)
The original form requires the subquery to be evaluated (by index access
to NewCookieMap) for every row of USERS.
The second does a full scan of NewCookieMap and then accesses only the
qualifying rows of USERS.
In this case, of course, ensure that there is also an index on Users
(cookie [,...)
Hope this helps.
-- Chrysalis PS. Before anyone tells me that the optimiser should treat both forms of the query in the same way, I invite him/her to consider the case where NewCookieMap has no rows: the two forms are NOT directly equivalent. FABRICATI DIEM, PVNC ('To Protect and to Serve') Terry Pratchett : "Guards Guards"Received on Tue Jul 15 1997 - 00:00:00 CDT
![]() |
![]() |