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

From: <dtang_at_minn.net>
Date: 1997/11/04
Message-ID: <63o38t$4c$1@darla.visi.com>#1/1

Hi:

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

	field_1,
	field_2

)

Table B
(
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)

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 CST