db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Derby-573 Optmier overrides - joinOrder and joinStrategy
Date Wed, 08 Feb 2006 20:55:32 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
I am not sure why joinStrategy <u><b>must </b></u>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.<br>
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.<br>
Any other thoughts?<br>
Mamta Satoor wrote:<br>
  <div>One of optimizer overrides, that&nbsp;a user can specify, is
joinStrategy.&nbsp;The functional spec for joinStrategy says following "The
joinStrategy property can be used only within a TableExpression, <u>but
it must be used in conjunction with the joinOrder property. Do not let
the optimizer choose the join order.
  <div>When I wrote the functional spec, I got this piece of
information from old Cloudscape release documentation, where the
optimizer overrides were available&nbsp;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
  <div><strong>SELECT * FROM t1 a, t1 b --DERBY-PROPERTIES
WHERE a.c1=b.c1</strong></div>
  <div>As you can see, the example above doesn't specify joinOrder
override&nbsp;along with joinStrategy. Should&nbsp;Derby (in its code) make it
mandatory that joinStrategy can't be specified w/o joinOrder? If not,
then should the functional spec&nbsp;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. </div>

View raw message