Re: Join order and access path

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
60A6.3050400-FH/oHRRPlqlVF0LlKHyXCA@xxxxxxxxxxxxxxxx%3e

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