Three ideas:
1. Have you run analyze on the tables? If you haven't, that would explain
it. If you have, its probably doing the right thing, because,
2. If you are retrieving more than 5-15% of the rows in a table, it is
actually faster to use a full table scan, because of the additional io
required to read the index. (This number changes to 50% or higher if you use
a hash cluster, and goes even higher if all of the columns you're reading
are in the index.)
3. If you have analyzed, and want to over-ride 2, then you can use a hint.
It's a comment on the first line of the query, I believe the form for what
you want is:
/* +INDEX <tablename> (<index_name>) */
have fun!
-Amos
dtang_at_minn.net wrote in message <63o38t$4c$1_at_darla.visi.com>...
>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 Wed Nov 05 1997 - 00:00:00 CST
