| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Hom to force optimizer to use certaim index when doing update?
On Tue, 04 Nov 1997 23:15:20 GMT, dtang_at_minn.net wrote:
>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)
>
Well, according to the above update, we have no choice but to full scan tableA as according to the update statement, every row in A will be updated. There is no where clause on tableA.
Have you tried:
update tablea A
set a.field_1 = (select b.field_1 from tableb B where a.field_2 = b.field_2)
where a.field_2 in (select b.field_2 from tableb B)
/
which, since it has a where clause on tableA, will tend to full scan B, creating the unique set of b.field_2 values and index range scanning A to find those rows.
btw: the first query probably would have failed in real execution. Without the predicate on TableA, every row in A would have to have a match in TableB else the subquery would return zero rows but a subquery used with equals must return at least one and at most one row. The update would fail on the first row in A that didn't have a match in B.
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.
I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Tue Nov 04 1997 - 00:00:00 CST
 |
 |