db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6308) joinStrategy optimizer override is ignored if you don't also specify joinOrder
Date Wed, 31 Jul 2013 19:37:51 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6308?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13725636#comment-13725636
] 

ASF subversion and git services commented on DERBY-6308:
--------------------------------------------------------

Commit 1509000 from [~rhillegas] in branch 'code/trunk'
[ https://svn.apache.org/r1509000 ]

DERBY-6308: Correct type in the license header of OptimizerOverridesTest; commit derby-6308-01-aa-correctLicenseHeader.diff.
                
> 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-01-aa-correctLicenseHeader.diff, 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

Mime
View raw message