Hom to force optimizer to use certaim index when doing update?

Hi:

[Quoted] [Quoted] I get very tricky problem. I want to do a bunch update between two tables, say:
Table A
(

	field_1,
	field_2

What I want to do is, update tableA.field_1 to tableB.field_1 for every row in tableA whose field_2 equals table_b.field2

Here is my sql statement:
UPDATE tableA A
SET A.field_1 =
( SELECT B.field_1 FROM tableB B WHERE A.field_2 = B.field_2)

[Quoted] The deal is that table A is a huge table (10M records), and B is a small table( about 10 thousand rows). Now execution plan shows oracle will do a full table sacn on table A, even I do index on tableB field_2, and table1 field_2).
What I want to do is to force optimizer to use tableB as the driving table, do full table scan on tableB, and then do index access on tableA). I just can't make it.

Is any guru have any thought on this issue.

Please email me at dtang_at_minn.net

Dong Received on Tue Nov 04 1997 - 00:00:00 CET