Derby optimizer hints?

[At...@rapid7.com]

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?

Thanks,
Ateesh

Re: Derby optimizer hints?

[At...@rapid7.com]

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.

-Ateesh






"Mamta Satoor" <ms...@gmail.com>
07/07/2006 11:32 PM
Please respond to "Derby Discussion"
 
        To:     "Derby Discussion" <de...@db.apache.org>
        cc: 
        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. 
 
Mamta

 
On 7/7/06, Ateesh_Verma@rapid7.com <At...@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? 

Thanks, 
Ateesh 

Re: Derby optimizer hints?

[Mamta Satoor <ms...@gmail.com>]

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.

Mamta


On 7/7/06, Ateesh_Verma@rapid7.com <At...@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?
>
> Thanks,
> Ateesh
>