Optimizing Query

[Erwin]

Can anybody tell me how to improve the performance of this query?

SELECT
NID, LEVEL, VALUE
FROM
DATA
WHERE
DID=2 AND STATUS=0

The value of STATUS is either 0 or 1. The primary key of DATA is EID
which I don't use in the query.

The total cost of the query without index is 240.47
If I put index on DID and STATUS, the total cost is 190.47.

Thanks

Erwin

[Joerg Ammann]

[email]erwin2@myrealbox.com[/email] (Erwin) wrote in message news:<43ecf6d7.0308130142.3091870d@posting.google. com>...

> Can anybody tell me how to improve the performance of this query?
>
> SELECT
> NID, LEVEL, VALUE
> FROM
> DATA
> WHERE
> DID=2 AND STATUS=0
>
> The value of STATUS is either 0 or 1. The primary key of DATA is EID
> which I don't use in the query.
>
> The total cost of the query without index is 240.47
> If I put index on DID and STATUS, the total cost is 190.47.
>
> Thanks
>
> Erwin

datatype of STATUS ?
did you a
runstats WITH DISTRIBUTION
on the table?, otherwise the optimizer does not know that there are
only 2 different values for STATUS.

[Erwin]

> datatype of STATUS ?

I use SMALLINT. Actually, I need only two values for STATUS.

> did you a
> runstats WITH DISTRIBUTION
> on the table?, otherwise the optimizer does not know that there are
> only 2 different values for STATUS.

I didn't execute RUNSTATS WITH DISTRIBUTION on the table. I try to
execute RUNSTATS WITH DISTRIBUTION, and it works.

Thanks :)