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.
-- Jeremy
