Rick Hillegas wrote:
Hi Oyvind,
I agree that this is inelegant. As you note, this approach step by step
forces a plan which the current Derby optimizer is capable of
considering--with or without the covering index. Regardless of whether
we teach the optimizer some better tricks, I think it's worth beefing up
our support for in-memory temporary tables:
o There are always deceptively simple queries which the optimizer
misjudges. It's good to give the customer tools for getting unstuck
while they wait for the bugfix release.
o Often the customer knows facts about the data which the optimizer
can't plausibly learn.
Yes, I agree with you.
o The current Derby optimizer is capable of considering only a very
limited subset of the useful plans.
That reminds me of a very entertaining presentation which was held at
VLDB this year:
Slides: http://www.vldb2005.org/program/slides/fri/s1228-reddy.ppt
Paper: http://www.vldb2005.org/program/paper/fri/p1228-reddy.pdf
Have a look, it's worth the time.
We should definitely consider more execution plans in Derby, so that we,
too, could draw such interesting pictures. ;o)
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/