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">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<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>
<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>
<br>
Any other thoughts?<br>
<br>
Satheesh<br>
<br>
Mamta Satoor wrote:<br>
<blockquote
 cite="midd9619e4a0602081111l56358482i719e6641242ffa12@mail.gmail.com"
 type="cite">
  <div>Hi,</div>
  <div>&nbsp;</div>
  <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.
  </u>"</div>
  <div>&nbsp;</div>
  <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>
  <div><strong>SELECT * FROM t1 a, t1 b --DERBY-PROPERTIES
joinStrategy=nestedloop<br>
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>
  <div>&nbsp;</div>
  <div>thanks,</div>
  <div>Mamta</div>
</blockquote>
</body>
</html>


Mime
View raw message