Re: Full table scans/indexed read

And are the 'init' file settings the same on both db's, especially things like multi_block_read_count, etc.

MotoX.

Hans-Peter Sloot wrote in message <01bdc107$9fa4ee40$0a0c010a_at_hans_peter>...
>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 - 02:15:08 CDT