Re: How to tune up this simple query

EXPLAIN PLAN and TKPROF.

Go to http://technet.oracle.com to find out how.

Daniel Morgan

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 Mon Jan 28 2002 - 10:33:18 CST