Re: Full table scans/indexed read

Are you sure that the rule based optimizer is used for both databases? You say that statistics are not updated but you should be sure that there are no statistics at all.

Have you explained the query on both databases?

Scott Patterson <scott.patterson_at_trilogy.com> wrote in article <35c8ce8d.0_at_feed1.realtime.net>...
> 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 - 01:58:32 CDT