MThomas wrote:
> Hi, Brian:
>
> Thanks for the quick response.
>
> Yes we are using the CBO, and I just updated the statistics for the table
> (using the DBMS_STATS package as you suggested). Unfortunately the optimizer
> still seems to think the full table scan is faster (not sure why, in my mind
> this would be extordinarily slow in comparison).
>
> Is there a way to force it to use the index? I have been looking at hints,
> but so far I haven't quite figured out how to use them correctly.
>
> Thanks,
>
> Mark.
>
>
>>I'm assuming that you are using CBO. The CBO is determining that index
>>lookups with the third value is more costly than a full table scan. The
>>first thing to do is to make sure that you have up to date statistics.
>>And use the DBMS_STATS package, not the ANALYZE command.
>>
>>HTH,Brian
>>
>>MThomas wrote:
>>
>>>Good morning:
>>>
>>>I am having some difficulty understanding the behaviour of Oracle 9i
>>>(9.2.0.2 on Windows 2000 Server) during a retrival.
>>>
>>>The query is in the form:
>>>SELECT * FROM iohistory
>>> WHERE ioid IN (63515, 63516)
>>> AND reportdatentime >= '25-Mar-2003'
>>> AND reportdatentime < '21-Jun-2003'
>>>
>>>When the query is executed for one or two ioid values , the proper index
>>
> is
>
>>>used. However when a third point is addes to the retrieval a full table
>>>scan is initiated (this is a problem as the table currently contains
>>>~60,000,000 records). Ideally it would always (or nearly always) use
>>
> the
>
>>>index.
>>>
>>>The index is unique on the ioid and reportdatentime columns of the
>>
> table.
>
>>>Would anyone have an idea how I may correct this behaviour?
>>>
>>>Thanks for your help.
>>>
>>>Mark.
>>
>>--
>>===================================================================
>>
>>Brian Peasland
>>oracle_dba_at_remove_spam.peasland.com
>>
>>Remove the "remove_spam." from the email address to email me.
>>
>>
>>"I can give it to you cheap, quick, and good. Now pick two out of
>> the three"
>
>
>
>
introduce optimizer parameters into you spfile, or init.ora.
Check out the document "The Search For Intelligent Life
In The Cost-based Optimizer" by Tim Gorman
--
Regards, Frank van Bortel
Received on Mon Jun 23 2003 - 15:13:32 CDT
