Full table scans/indexed read

I have an interesting problem. I have two databases with identical structures and similar data. Both are Oracle 7.3 using rule based (set to 'choose' without updating statitics). Taking the same query one is using a full table scan while the other is correctly using the index. The table is quite large so the execution time is 2+ minutes for the full table scan. The indexed query is sub second. Both databases have had the main table exported and re imported in the past day.

The query is:
select t1.fld1, t1.fld2, t1.fld3 from table1 t1, table2 t2, table3 t3 where t2.primary_key = t1.primary_key

and     t3.primary_key = t1.primary_key
and     t1.primary_key in (  a list of 50 literal values);

I can not change the query its self. I am just looking for a reason why one database would choose to do full table scans.

Ideas?

thanks

Scott Received on Wed Aug 05 1998 - 18:28:35 CDT