SQL query timing question

I am confused about the length of time a certain type of query is taking and was hoping someone might have some insight. The table is large (100,000 rows) but all the queries are on indices. The 3 columns involved are: num_id, type, status. num_id, type are part of the primary_key (the third column in the primary key is not used in this query) status is a separate index. The first query I do is:

        select count(*) from table where num_id=1000 and type='G'

For a count of 1000 rows the query returns in less than 1 second.

The second query is:

        select count(*) from table where num_id=1000 and status='N'

This query takes 3 minutes!! In this case, all the rows with num_id=1000 have a type of 'G' and a status of 'N' - so the count is equivalent.

I then did a 3rd query:

        select status,count(*) from table where num_id=1000 group by status

This query returned in about 1 second.

I did an explain plan to be sure Oracle was using indices in all cases, and it was. Is 3 minutes for the second query reasonable when the first and third would seem to have equivalent requirements? Any insight would be appreciated.

Joanne Woytek
Code 902.2
NASA/GSFC
joanne_at_eosdev1.gsfc.nasa.gov Received on Mon Jun 05 1995 - 00:00:00 CEST