Re: Full table scans/indexed read

Thanks to all who responded here and via email. There was an error in the information in my original message. The remote database was experimenting with cost based optimization (not rule based as I stated). By dropping the statistics, performance jumped back to where it should have been.

Scott

MotoX wrote in message
<902387621.11620.0.nnrp-02.c2de712e_at_news.demon.co.uk>...
>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 Fri Aug 07 1998 - 11:15:44 CDT