Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Index usage question
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"Received on Mon Jun 23 2003 - 11:06:11 CDT
![]() |
![]() |