Greetings-
We've recently had a problem at two different customer sites (that we know
of) where the query optimizer seems to be ignoring an index for a simple
join. The query is simply:
SELECT tbl2.*
FROM tbl1, tbl2
WHERE tbl1.col1 = tbl2.col2
AND tbl1.col3 = ?someinteger
Col1 has a primary key index, col2 has a regular index (with many distinct
values), and col3 also has a regular index (with many distinct values). Tbl1
has 300,00 rows, tbl2 has 3 million rows. We are retrieving ~20 rows from
tbl1 and there are ~10 rows in tbl2 for every row in tbl1, so the query
should retrieve ~200 rows. We are allowing statistics to be automatically
updated by SQL on all indexes.
At first, the query ignores the col2 index and the query analyzer shows it
is doing table scan. Then we re-create the col2 index and re-run the query
and the query analyzer now shows it's being used. Any ideas what's going on?
And other than the slow performance, is there any way to detect the problem?How frequently do you update statistics for those tables? Is
AUTO_UPDATE_STATISTICS ON or OFF?
I'd also suggest you stop using the deprecated join syntax, and use
something like:
SELECT tbl2.*
FROM tbl1
inner join tbl2
on tbl1.col1 = tbl2.col2
AND tbl1.col3 = ?someinteger
ML|||Thanks for the suggestions. AUTO_UPDATE_STATISTICS is ON and the indexes are
also set to be automatically updated. I did notice torn page detection was
turned off for that database, which might have contributed to the problem if
it was truly corruption. If a client has this problem again, we'll try
updating the statistics first.
"ML" wrote:
> How frequently do you update statistics for those tables? Is
> AUTO_UPDATE_STATISTICS ON or OFF?
> I'd also suggest you stop using the deprecated join syntax, and use
> something like:
> SELECT tbl2.*
> FROM tbl1
> inner join tbl2
> on tbl1.col1 = tbl2.col2
> AND tbl1.col3 = ?someinteger
>
> ML
No comments:
Post a Comment