Re: How to tune up this simple query

Ewong

You could try some of the following.

Analyze the fk index is the number of testid's is greater than 100,000 is low this may get the index used. I guess the CBO is expecting to bring back a lot of records and is therefore trying to miss out the hit of index and then table reads for the majority of the table.

Try creating a unique index on testid, id this should get a full fast scan with your query and be far quicker.

Hope you find a solution

Nathan

"Miguel Camba" <M.CAMBA_at_terra.es> wrote in message news:3c59daec.4509789_at_news.terra.es...
> Use tips
>
>
> On 28 Jan 2002 13:29:45 -0800, ewong_at_mail.com (Ed Wong) wrote:
>
> >I have this table named testresult with 10 million records:
> >id number(10) --pk, indexed
> >testid number(10) --fk reference to test.id, indexed
> >result varchar2(100)
> >
> >The following query takes 20 seconds:
> >select min(id) from testresult where testid > 100000;
> >
> >I have a program that does this query in a loop(because testid keep
> >changing). So 20 seconds is not acceptable.
> >
> >How to tune this up? How come it doesn't use index of testid? The
> >explain plan shows it's doing a full table scan instead of using index
> >of testid.
> >
> >Thanks,
> >ewong
>
Received on Sun Feb 03 2002 - 08:43:48 CET