Re: SQL query timing question

joanne_at_eosdev1.gsfc.nasa.gov (Joanne Woytek) wrote:
>

snip

> 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.
>
>

snip

>

In the second query the optomizer is probably using the index on the status when you don't wnat it to, try using

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

so the index on the num_id is used.

I hope this helps

Yours ever

eddy Received on Mon Jun 05 1995 - 00:00:00 CEST