Hi Mamta,

Thanks for the info.

Here is the scenario:

The query I am talking about is part of a repair mechanism when the application starts. So, if this query is invoked, it means that the application and hence the db connection was unexpectedly shutdown
while the INSERTS/UPDATES were being performed on that particular table. Table scans occur only the first time the query is run after the restart. Subsequent queries use the indices as expected.

Is it possible that since the db connection was interrupted unexpectedly, the index tables were not updated. So, when a query was run on that table, the optimizer detected that the index table is not up-to-date
and decided to do a table scan.

Also, when exactly are the indices updated in a transaction? Is it after the transaction is commited or with every update?

Let me know if there are any documents available on this.

Thanks again for your help.


"Mamta Satoor" <msatoor@gmail.com>

07/07/2006 11:32 PM
Please respond to "Derby Discussion"

        To:        "Derby Discussion" <derby-user@db.apache.org>
        Subject:        Re: Derby optimizer hints?

Hi Ateesh,


Optimizer hint support was added to Derby 10.2 as part of jira DERBY-573 : Provide support for optimizer overrides in Derby.


If you think you have come across some Derby optimization issue with your particular scenario, you should probably send more info on it to the Derby community and someone might be able to take a look to see if there is a real bug involved here.



On 7/7/06, Ateesh_Verma@rapid7.com <Ateesh_Verma@rapid7.com> wrote:

Is there a way to add hints to derby optimizer ?

For example, I have a query that is supposed to use an index. The optimizer mostly uses
the index but sometimes decides to do a table scan!

I have tried different transformations of the query but I see the same inconsistent behavior.

Any suggestions?