Re: 7.3.3 Query performance problem

kwalters_at_pbs.org (Ken Walters) writes:

>This query runs quickly and the explain plan show that it is using an
>index only range scan on the VarcharColumnB. There are about 600
>records with 'E' as a value for VarcharColumnB in a table with a over
>100K records.

>SELECT count(IntegerColumnA)
>FROM Table1
>WHERE VarcharColumnB = 'E';

>This query runs slowly and does a full table access on the table. Why?

>SELECT IntegerColumnA
>FROM Table1
>WHERE VarcharColumnB = 'E';

Maybe the object of the count(X) statement really doesn't matter in the first case, since you have the limiting WHERE clause. In the second case you need not only look at the WHERE clause but return the actual values of IntegerColumnA.

For example, if IntegerColumnA had a value of NULL in every record, the first query only has to look at the WHERE clause to produce an answer. The second query has to look at both the WHERE clause and the actual values of IntegerColumnA.

Query #2 would probably also be fast if you had an index covering VarcharColumnB and IntegerColumnA. Then it would be an index-scan to find the necessary information.

--
Chris Howard
daylight_at_frii.net Received on Wed Sep 16 1998 - 14:19:53 CDT