Re: Derby-573 Optmier overrides - joinOrder and joinStrategy

[Satheesh Bandaram]

I am not sure why joinStrategy must be used with joinOrder optimizer override... There may be a reason why joinStrategy is recommended or typical to be used along with joinOrder, but is there a reason why it must be so? I can see some reasons why joinStrategy makes sense to be used with joinOrder... especially for 'hash' joins. I looked at online Cloudscape 5.1 tuning guide... It says: "Note: Use this optimizer override with the joinOrder property only. Do not let the optimizer choose the join order." That seems like a good advise, though it doesn't say it must be used like that. Any other thoughts? Satheesh Mamta Satoor wrote: Hi,   One of optimizer overrides, that a user can specify, is joinStrategy. The functional spec for joinStrategy says following "The joinStrategy property can be used only within a TableExpression, but it must be used in conjunction with the joinOrder property. Do not let the optimizer choose the join order. "   When I wrote the functional spec, I got this piece of information from old Cloudscape release documentation, where the optimizer overrides were available in a non-portable syntax. I also got some of the test cases from that old Cloudscape release and one of the test cases is as follows SELECT * FROM t1 a, t1 b --DERBY-PROPERTIES joinStrategy=nestedloop WHERE a.c1=b.c1 As you can see, the example above doesn't specify joinOrder override along with joinStrategy. Should Derby (in its code) make it mandatory that joinStrategy can't be specified w/o joinOrder? If not, then should the functional spec be changed? What happens if user doesn't specify joinOrder along with joinStrategy? Will joinStrategy be ignored. I haven't had a chance to investigate this yet. I only stumbled on it last night when I was reviewing Eric's hardwork on getting this feature documented and noticed that the example for joinStrategy didn't include joinOrder.   thanks, Mamta