db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: Optimizer hints?
Date Sat, 18 Dec 2004 03:36:08 GMT
Satheesh Bandaram wrote:
>    1. Enhance the SQL to recognize additional keywords, like properties.
> These properties could specify optimizer hints. That is what Cloudscape
> had before, but this causes non-portable SQL to be written. Attempts to
> run these statements against any other SQL server could cause syntax
> errors. Not an ideal solution, according to me.
>    2. Provide optimizer hints as SQL comments. These comments are
> recognized only by Derby parser and other SQL engines would simply
> ignore these. There are some limitations in Derby parser currently to
> implement this option.
>    3. Provide an offline tool which registers hints for SQL statements.
> When those SQL statements are executed, Derby could look up previously
> registered hints for those statements and automatically apply them.
> Advantage of this scheme is that applications don't need to be modified
> to add hints and they can easily be removed when not needed. How exactly
> to register the hints could be worked out. I personally prefer this
> approach.

The challenge I see with 3) is how the hints are associated with a 
specific query. If matching is done using the query text then any slight 
modification in the query will break the match resulting in an un-hinted 
plan. So a minor change in application code could have a big impact on 
performance simply because a sub-optimal plan was used.

This could be addressed by providing a key to match on in the query text 
(e.g. in a comment) but that seems to raise the same issues as simply 
embedding the hint whilst still adding the additional complexity 
associated with administering the hint data.

I would prefer using the same mechanism as most other databases: 
embedding hints in comments.


View raw message