Jeffrey - I did think of another issue we could use your input on. In Dec 2004, another thread raised a question about the possibility of adding optimizer hints:
http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200412.mbox/%3c41C3 [EMAIL PROTECTED] Some related questions in order of my own priority: 1. Since you wrote the original optimizer if you were currently the lead architect what would your recommendations be for enhancing or improving the optimizer? 2. What other optimizer architectures or features did you consider and reject during the original design and development? Would you recommend any of these for reconsideration now? 3. Could hints be used to good advantage in Derby? 4. What type of hints might be most effective? A. Index hints (index: t1-zip_code)? B. Join order hints (join-order: t3, t2, t4, t5, t1)? C. Join type hints (join-type: hash-table/nested-loop/other)? D. Plan_table hints? Store an execution plan in a table to always used for a particular statement E. Other hints? 5. What 'hint' implementation approach would you recommend given the current architecture? Are changes needed to the architecture to effectively implement your recommendation? 6. Would it be possible to implement a 'force' option that would force Derby to use a particular hint rather than simply 'suggest' an approach? This could be very useful especially during dev/test: it may be harder to compare two execution plans if you can't force Derby to use each plan for exactly the same set of conditions. 7. What information is currently available from the optimizer, via logs or a debug setting, to show the explain plan and/or list the options (e.g. join order) that were considered, including options that were not chosen? Is there additional information that would be useful that could be easily extracted? 8. Are there others database, table or index statistics that could be captured that might faciitate optimization? 9. Other possible plan-table strategies? For example, store in a plan_table information about table relationships (master/detail, fact/dimension) so that certain table combinations are always joined the same way regardless of the query? Sorry to barrage you with questions. No one's trying to 'rope' you into rejoining the project. But I doubt if anyone would object very strenously if you chose to do so ;-) +1
