You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2013/07/30 18:03:48 UTC
[jira] [Updated] (DERBY-6308) joinStrategy optimizer override is
ignored if you don't also specify joinOrder
[ https://issues.apache.org/jira/browse/DERBY-6308?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-6308:
---------------------------------
Attachment: DERBY_6308.java
Attaching DERBY_6308.java, a repro for this issue.
> joinStrategy optimizer override is ignored if you don't also specify joinOrder
> ------------------------------------------------------------------------------
>
> Key: DERBY-6308
> URL: https://issues.apache.org/jira/browse/DERBY-6308
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
> Attachments: DERBY_6308.java
>
>
> Using the schema from OptimizerOverridesTest, the following query...
> select * from --DERBY-PROPERTIES joinOrder=FIXED
> t1 a, t1 b --DERBY-PROPERTIES joinStrategy=NESTEDLOOP
> where a.c1 = b.c1
> ...correctly produces this plan: ( T1_C1C2C3 * T1_C1C2C3 )
> However, if you omit the joinOrder directive, the resulting query...
> select * from
> t1 a, t1 b --DERBY-PROPERTIES joinStrategy=NESTEDLOOP
> where a.c1 = b.c1
> ...unexpectedly produces a hash join order: ( T1_C1C2C3 # T1_C1C2C3 )
> The Tuning Guide says that you should specify the joinOrder directive in order to get optimal results, but the Tuning Guide does not indicate that the joinOrder directive is required in order to activate the joinStrategy directive. See the Tuning Guide section titled "Overriding the default optimizer behavior".
> Is this behavior expected? If so, should we change the Tuning Guide to say that the joinOrder directive is required in order to activate the joinStrategy directive?
> For reference, here is the same query with a new-style optimizer plan override...
> select * from t1 a, t1 b where a.c1 = b.c1--derbyplan ( T1_C1C2C3 * T1_C1C2C3 )
> ...which produces the expected plan: ( T1_C1C2C3 * T1_C1C2C3 )
> I will attach a repro.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira