Re: Hom to force optimizer to use certaim index when doing update?
Date: 1997/11/05
Message-ID: <01bce9c8$968c0120$54110b87_at_clamagent>#1/1
> 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.
Your request doesn't make sense. You're updating *every* row in tableA, so
of course you're doing a full scan. You'd only slow things down going to
the tableA's index for each row update. Rewrite the select to WHERE
B.field_2 = A.field_2
since for each row in A you want the matching B. If tableB is small enough,
or the SGA large enough, the entire able will fit in memory and the lookup
on B will be very fast.
-djc
Received on Wed Nov 05 1997 - 00:00:00 CET