Re: Full table scans/indexed read

By default, an import will do an 'analyze statistics' after importing the table. (Actually, this is controlled on the export ). My guess is that one of the tables had an analyze done upon import, but the other didn't?

Scott Patterson wrote:

> 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 Thu Aug 06 1998 - 07:07:32 CDT