Re: need help with an update

Gurelei wrote:
>
> Hi all:
>
> May be someone can help me to tune this update ...
>
> update A1 a
> set a.empl_hrchy_id =
> (select maxid from B1 b
> where a.usr_id = b.usr_id)
> where exists
> (select 'x' from B1 where usr_id = a.usr_id)
>
> Table A1 has several million rows (5 to 6), table
> B1 has no more than 5 thousand rows.. A1 is indexed
> on the fields, usr_id, usr_id/empl_hrchy_id
> and empl_hrcy_id/usr_id. B1 has no indices. The
> explain plan shows a full table scan against the A1
> table, which is something I'd like to avoid. Is there
> a way to make Oracle use an index instead (without
> hints)?
>
> thanks
>
> Gene
>

Gene,

  so you're another victim of the 'always use EXISTS and never IN' propagandists? EXISTS is fine when you have other criteria allowing you to narrow to a very small subset. Otherwise you are better off without it. You cannot do otherwise than a full scan of A1 with such a query. Try IN instead. Perhaps that a hint about in-list iterators (forgot the name) will help though.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).