Re: How to tune up this simple query

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

AFAIK, the use of group functions will force a full scan (or more correctly, no index usage). This being the case, maybe you will need to look at different way of doing this.

If the above is not true:

1. How do you knwo it does not use the index?
2. Have you run this through tkprof?
3. Are you running CBO or RBO?
4. What is your compatible parameter?
5. Do you have any of the index parameters set? What are their values? -- Ron Reidy Oracle DBA