need help with an update

From: Gurelei <gurelei_at_yahoo.com>
Date: Mon, 05 May 2003 09:31:55 -0800
Message-ID: <F001.0058FC02.20030505093155@fatcity.com>

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

---

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gurelei
  INET: gurelei_at_yahoo.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).

Received on Mon May 05 2003 - 12:31:55 CDT