Re: Increased query time adding an index

Giancarlo Sanna <75824348drop-out-this_at_it.ibm.com> wrote in message <3587DDA8.43D246B3_at_it.ibm.com>...
>I added an index to a table in order to speed up a query in my
>application.
>The query, after the index creation, improved its performances but
>another query (a join on several tables one of which was the table
> on which I added the index) increased its execution time from four
> seconds to more than an hour (I actually killed the process after one hour).
>How is it possible? Why the optimizer doesn't simply ignore the new
>index if it is not useful for him?
>Moreover I added another index on the columns that were present in the
>where condition of the second query but until I dropped the first index
> the second query didn't work.
>
>Can anybody help me?
>

First of all... What optimizer is used? Cost-based or rule-based? With rule-based optimizer it is very possible to spoil performance with adding indexes. More/less it chooses execution plan which is the best in rank of possible plans. Adding index may force using another plan which is better in rank but implies excessive full scans on large tables. Other reason may be that index-scanning a significant part of a large table is much worse than ordinary full-scan. In such cases the solution may be using hints in queries for example. In some cases adding another indexes may be necessary :-(

But if cost-based optimizer is used, first ensure that all statistics that you may need are computed.

Piotr
--
 Piotr Kolodziej
 <pkol_at_otago.gda.pl>

• My voice. My private opinion. *