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 "Mamta A. Satoor (JIRA)" <de...@db.apache.org> on 2005/09/16 05:40:54 UTC

[jira] Created: (DERBY-573) Provider support for optimizer overrides in Derby.

Provider support for optimizer overrides in Derby.
--------------------------------------------------

         Key: DERBY-573
         URL: http://issues.apache.org/jira/browse/DERBY-573
     Project: Derby
        Type: New Feature
  Components: SQL  
    Versions: 10.2.0.0    
    Reporter: Mamta A. Satoor
 Assigned to: Mamta A. Satoor 


Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Attachment: derby572OptimizerOverridesAndUpgrade121205.txt

Submitting another patch to JIRA after doing a sync on the client (The Grant/Revoke checkin touched quite a bit of the files that I have modified). I have run the tests and no new failures with the patch.

> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Resolved: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]
     
Mamta A. Satoor resolved DERBY-573:
-----------------------------------

    Fix Version: 10.2.0.0
     Resolution: Fixed

All the code changes for this feature are already checked in. Have entered Derby-855 as a subtask for the documentation changes

> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>      Fix For: 10.2.0.0
>  Attachments: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt, derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12330025 ] 

Daniel John Debrunner commented on DERBY-573:
---------------------------------------------

I don't believe that  we should assume that every multiple database application is going to use a code generator, or that Derby's syntax should force application developers into using one. It is possible to write an application that works against multiple databases, thus the use of comments to provide hints seems a reasonable approach.

It may  make more sense for the lead-in to be something like DERBY-HINTS or DERBY-PROPERTIES rather than the generic term PROPERTIES.

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12330018 ] 

Rick Hillegas commented on DERBY-573:
-------------------------------------

Thanks, Mamta, for pointers to these two threads. There is some discussion here about where to put optimizer threads and how to bind them to specific queries. The consensus seems to be to embed hints in individual queries. No argument there.

However, these threads do not address the problem of the parser silently ignoring mis-typed optimzer directives. I think this is a brittle user api and a serious issue with the current functional spec.

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12362904 ] 

Satheesh Bandaram commented on DERBY-573:
-----------------------------------------

I have submitted this fix to trunk. Thanks for enhancing RUNSTATs to show user specified optimizer hints are being used!

Transmitting file data .....................................
Committed revision 369619.


> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt, derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Attachment: derby573OptimierOverrides110805.txt
                optimizeroverrides.html

I have the patch for optimizer overrides support in Derby. Alongwith the patch, I have attached the updated functional spec to the JIRA entry Derby-573.

Majority of the changes went into the sqlgrammar.jj because Derby engine already has support for them internally. It is the parser that needs to recognize these overrides and pass it on to through the query nodes. The parser now looks for character sequence -- DERBY-PROPERTIES (case insensitive and space between -- and D is optional) and once it finds that, it looks for propertyName=value pairs on that same comment line in parser's propertyList method. The parser does the basic check to make sure that the same property is not used more than once for a given table. The remaining checks on the properties like checking the existence of user specified index etc are done in the bind phase.

I also changed the metadata.properties file to use --DERBY-PROPERTIES rather than old PROPERTIES clause to supply optimizer overrides. In addition, added \n at the end of the optimier override comment lines to make sure the comment line does not get concatenated with the next line of the sql.

Import.java had to be changed to user --DERBY-PROPERTIES rather than PROPERTIES.

Added a new test optimizerOverrides.sql which runs in both embedded and network server mode.

Rerunning all the tests after syncing the codeline to make sure nothing has broken. An earlier run of the tests before the sync came out clean.

I plan to next work on exposing these overrides through runtime statistics so that user can verify that the optimizer overrides are getting used.

I haven't researched into upgrade much but will the changes in metadata.properties require some upgrade path for existing databases? Any pointers here will be very useful.

svn stat
M      java\engine\org\apache\derby\impl\load\Import.java
M      java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M      java\engine\org\apache\derby\impl\jdbc\metadata.properties
M      java\engine\org\apache\derby\iapi\reference\SQLState.java
M      java\engine\org\apache\derby\loc\messages_en.properties
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\db2Compatibility.sql
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\copyfiles.ant
A      java\testing\org\apache\derbyTesting\functionTests\tests\lang\optimizerOverrides.sql
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\checkConstraint.sql
M      java\testing\org\apache\derbyTesting\functionTests\tests\store\access.sql
A      java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\optimizerOverrides.out
M      java\testing\org\apache\derbyTesting\functionTests\master\db2Compatibility.out
A      java\testing\org\apache\derbyTesting\functionTests\master\optimizerOverrides.out
M      java\testing\org\apache\derbyTesting\functionTests\master\checkConstraint.out
M      java\testing\org\apache\derbyTesting\functionTests\master\access.out
M      java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall
M      java\testing\org\apache\derbyTesting\functionTests\suites\derbynetmats.runall

Comments/questions on the patch?


> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Attachment: optimizeroverrides.html

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Attachment: derby572OptimizerOverridesAndUpgrade121105.txt

I have addressed Dan's comments with the latest patch attached to the JIRA entry. 

In addition, I have also added a master file for the phaseTester test in java\testing\org\apache\derbyTesting\functionTests\master
Output of the manual run of this test can be diffed against the master that I am checking in. In future, if anyone changes this test such that it's output will change, then they should change the master file accordingly. This way, we can make sure that the test is still running correctly as it is evolving. Once this test is run as part of a suite like other tests, we will not have to run this test manually and any changes to master will be caught easily,


> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12330031 ] 

Mamta A. Satoor commented on DERBY-573:
---------------------------------------

I can look into modifying the RunTimeStatistics to indicate that user overrides are being used. Also, I will see how parser can be modified to look for --DERBY-HINTS rather than just --PROPERTIES. If people prefer DERBY-PROPERTIES over DERBY-HINTS, please put comment stating so to this JIRA entry.

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12360326 ] 

Satheesh Bandaram commented on DERBY-573:
-----------------------------------------

This patch has been committed to trunk.

Transmitting file data ........................................
Committed revision 356562.

It seems you have also resolved DERBY-727 along with this change, so please mark both of them as RESOLVED and close them after testing on trunk build. You figured out several hard issues here 1) Ability to extract hints provided in comments 2) Soft upgrade issues and 3) adding upgrade infrastructure for 10.2. Good job!


> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Attachment: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt

I have attached a patch named Derby573OptimizerOverridesAndRunTimeStatistics011206.txt to JIRA Derby573 Provide support for optimizer overrides in Derby. This patch enables users to see the optimizer overrides specified in the sql as part of runtime statistics info. This is achieved by changing the 
generator so that these properties get passed from compile time to execute time. This change in generate phase can be found in FromBaseTable,
BaseJoinStrategy and JoinNode. The changes in the other classes is for returning the correct number of arguments to the scan. That change is in 
getScanArgs method. 

In addition, I have changed the existing lang/optimizerOverrides.sql to test this patch. derbyall suite has run fine on my Windows XP m/c with Sun's jdk14.

svn stat 
M      java\engine\org\apache\derby\impl\sql\compile\BaseJoinStrategy.java
M      java\engine\org\apache\derby\impl\sql\compile\NestedLoopJoinStrategy.java
M      java\engine\org\apache\derby\impl\sql\compile\SelectNode.java
M      java\engine\org\apache\derby\impl\sql\compile\JoinNode.java
M      java\engine\org\apache\derby\impl\sql\compile\HashJoinStrategy.java
M      java\engine\org\apache\derby\impl\sql\compile\FromBaseTable.java
M      java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M      java\engine\org\apache\derby\impl\sql\compile\HalfOuterJoinNode.java
M      java\engine\org\apache\derby\impl\sql\execute\HashLeftOuterJoinResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\HashJoinResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\LastIndexKeyResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\GenericResultSetFactory.java
M      java\engine\org\apache\derby\impl\sql\execute\DistinctScanResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\HashScanResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\InsertResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\TableScanResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\rts\RealNestedLoopLeftOuterJoinStatistics.java
M      java\engine\org\apache\derby\impl\sql\execute\rts\RealHashLeftOuterJoinStatistics.java
M      java\engine\org\apache\derby\impl\sql\execute\rts\RealHashJoinStatistics.java
M      java\engine\org\apache\derby\impl\sql\execute\rts\RealJoinResultSetStatistics.java
M      java\engine\org\apache\derby\impl\sql\execute\rts\RealTableScanStatistics.java
M      java\engine\org\apache\derby\impl\sql\execute\rts\RealNestedLoopJoinStatistics.java
M      java\engine\org\apache\derby\impl\sql\execute\RealResultSetStatisticsFactory.java
M      java\engine\org\apache\derby\impl\sql\execute\DependentResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\BulkTableScanResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\MergeJoinResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\NestedLoopJoinResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\NestedLoopLeftOuterJoinResultSet.java
M      java\engine\org\apache\derby\impl\sql\execute\JoinResultSet.java
M      java\engine\org\apache\derby\iapi\sql\compile\Optimizable.java
M      java\engine\org\apache\derby\iapi\sql\execute\ResultSetFactory.java
M      java\engine\org\apache\derby\iapi\reference\SQLState.java
M      java\engine\org\apache\derby\loc\messages_en.properties
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\optimizerOverrides.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\optimizerOverrides.out
M      java\testing\org\apache\derbyTesting\functionTests\master\DerbyNetClient\optimizerOverrides.out
M      java\testing\org\apache\derbyTesting\functionTests\master\optimizerOverrides.out


> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt, derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12329952 ] 

Mamta A. Satoor commented on DERBY-573:
---------------------------------------

There was discussion about various possible options to support optimizer hints in the past. One thread on this is titled "Optimizer hints?" at http://mail-archives.apache.org/mod_mbox/db-derby-dev/200412.mbox/%3c41C360A6.3050400@Sourcery.Org%3e. The other thread is titled "Join order and access path" at http://mail-archives.apache.org/mod_mbox/db-derby-dev/200502.mbox/%3cBAY3-F62B405CA29E874AB32DA5CF6F0@phx.gbl%3e. In the absence of SQL standard way of implementing this and based on the 2 email threads, I proposed to implement this with special comments --PROPERTIES.

Also, since the time I wrote the functional spec, I have been tweaking the parser to ignore white spaces(this includes " ", "\t") between -- and PROPERTIES to make parser more flexible about how a user specifies the properties. My work in the parser to support the optimizer hints is not completely finished. Hope to make more progress in couple days.

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12329897 ] 

Rick Hillegas commented on DERBY-573:
-------------------------------------

It sounds as though this syntax will not allow the parser to catch typos and that if the user makes a mistake, the parser will silently do the wrong thing. I'm not a big fan of silent failures.

Why prefer an Oracle-style, comment-based approach to Sybase-style, parser-checked optimizer hints? Is it because we hope that we can run the same query against Oracle and Derby by simply seeding the query with two sets of comments, one for each database? I don't understand the value of this flexibility. If the application really is going to run against two databases, then the developers will have to build a sql-portability layer anyway. In that case, queries won't be hard-coded: they will be constructed by a code generator. The code generator will have to be smart enough to handle other differences between the databases and it's no additional trouble for the code generator to emit database-specific optimizer hints.

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Closed: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]
     
Mamta A. Satoor closed DERBY-573:
---------------------------------


> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>      Fix For: 10.2.0.0
>  Attachments: Derby573OptimizerOverridesAndRunTimeStatistics011206.txt, derby572OptimizerOverridesAndUpgrade120605.txt, derby572OptimizerOverridesAndUpgrade121105.txt, derby572OptimizerOverridesAndUpgrade121205.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Attachment: derby572OptimizerOverridesAndUpgrade120605.txt

Here is the updated patch information which includes optimizer overrides support and database upgrade code. I have attached the patch to the Jira entry.

The code for optimizer overrides support is same as for the earlier patch. But since the metadata.properties has changed between 10.1 and 10.2 (because of the new user visible optimizer overrides syntax), we need to have proper upgrade code to support this change.

Following is a brief description of upgrade code
1)If a 10.1 db is getting run in soft upgrade mode with 10.2, the system tables would still have the old 10.1 optimizer overrides syntax which is not recognized by 10.2 The system tables of the 10.1 db can't be modified in soft upgrade mode to store the new 10.2 optimizer overrides syntax for metadata queries because in soft upgrade, system tables can't be modified in a backward incompatible way. To get around this, I have changed the code in EmbedDatabaseMetaData.java to see if db is getting run in soft upgrade mode. If yes, then it should read the metadata queries from metadata.properties rather than system tables. This will work because metadata.properties for 10.2 release has been modified to use the new optimizer overrides syntax.

2)If a 10.1 db is getting run in hard upgrade mode with 10.2, then we can simply drop the stored queries for metadata calls from system tables and put new queries from metadata.properties into the system table. This is acceptable in hard upgrade, because 10.2 db is not expected to run in 10.1 release. This code is in DD_version.java 

3)I have implemented the above 2 upgrade modes in a generic mode such that in future, with every new release, in soft upgrade mode, we will always read the metadata queries from metadata.properties. And in hard uprgade mode, we will drop and recreate the stored metadata queries in the system tables with the latest metadata queries from metadata.properties. This will take care of any future metadata.properties changes between the releases.

A little description on changes made to some of the java files for the upgrade code
1)Added new methods in Statement.java and LanguageConnectionContext.java which will allow us to recognize the queries coming from metadata calls and let such queries use internal SQL syntax. These internal SQL syntaxes are not available to an end user but we have some queries in metadata.properties which rely on them and hence we need to be able to run these queries with their internal syntax. This was not required in the past because metadata calls were always run from the system tables and hence the internal syntax was available to metadata calls at that point. Now, that in soft upgrade mode, we read the queries from metadata.properties file, we need to add these special methods to recognize metadata queries as special internal queries and let them use internal SQL syntax.
2)In DataDictionary.java, I have added 2 final static variables. One is added to reflect the 10.2 db version, DD_VERSION_DERBY_10_2. The other one is DD_VERSION_THIS_SOFTWARE_VERSION and with every release, it should be updated to the latest DD version final static number. This variable will be used by EmbeddedDatabaseMetaData to determine if we are in soft upgrade mode. Rather than hardcoding 10.2 DD version in EmbeddedDatabaseMetaData, I thought it would be better to use a generice variable DD_VERSION_THIS_SOFTWARE_VERSION.

In order to test my upgrade changes, I have used derbyTesting/upgradeTests/phaseTester.java which was checked in by Dan sometime back. I have changed this test to be a subclass of metadata.java and then I run the existing metadata tests within phaseTester after various stages of upgrade on a 10.1 db. phaseTester takes 4 parameters, the old derby major version, old derby minor version, old engine derby.jar classpath, new engine derby.jar and derbyTesting.jar classpath. An eg run is as follows
$ C:/p4clients/main/opensource/java/testing/org/apache/derbyTesting/upgradeTests/runphases 10 1 c:/p4clients/code101/opensource/jars/sane c:/p4clients/main/opensource/jars/sane
For the command above, phaseTester will 
1)first create a 10.1 db and then will run metadata tests on it.
2)Then run the 10.1 db with 10.2 software in soft upgrade mode and run the metadata tests on it (this is where the metadata sql will be picked from metadata.properties rather than system table)
3)The run the soft upgraded 10.1 db back with 10.1 software and rerun the metadata tests
4)Now run the 10.1 db in hard upgrade mode with 10.2 software. The db will be upgraded to 10.2 version (which means that stored metadata sql from system tables will be dropped and recreated with sql from metadata.properties). Run the metadata tests on this hard upgraded db
5)Finally, try to use this upgraded 10.2 db with 10.1 software and that will fail because we don't have backward compatibility.

This phaseTester test unfortunately is not part of derbyall and has to be run manually. I think we have a beetle entry Derby-514 to make this test available as part of a suite rather than requiring manual runs.

One another note on the tests, I have merged metadataJdbc20 and metadata.java into one single test by moving all the tests from metadataJdbc20 to metadata.java's super class metadata_test.java. In the past when we had support from jdk18, we had to have a separate
test for jdbc20 but since jdk18 support has been phased out, these tests can be merged.

Also, for reference, I have included the comments for the actual optimizer overrides changes from the previous patch.
Majority of the changes went into the sqlgrammar.jj because Derby engine already has support for them internally. It is the parser that needs to recognize these overrides and pass it on to through the query nodes. The parser now looks for character sequence -- DERBY-PROPERTIES (case insensitive and space between -- and D is optional) and once it finds that, it looks for propertyName=value pairs on that same comment line in parser's propertyList method. The parser does the basic check to make sure that the same property is not used more than once for a given table. The remaining checks on the properties like checking the existence of user specified index etc are done in the bind phase. 
 
I also changed the metadata.properties file to use --DERBY-PROPERTIES rather than old PROPERTIES clause to supply optimizer overrides. In addition, added \n at the end of the optimier override comment lines to make sure the comment line does not get concatenated with the next line of the sql. 
 
Import.java had to be changed to user --DERBY-PROPERTIES rather than PROPERTIES.
 
Added a new test optimizerOverrides.sql which runs in both embedded and network server mode.

Finally, I have run the derbyall suite and these changes didn't cause any new failures.

Please review and send in your comments.


> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: derby572OptimizerOverridesAndUpgrade120605.txt, derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: [jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram (JIRA) wrote:

>     [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12330020 ] 
> 
> Satheesh Bandaram commented on DERBY-573:
> -----------------------------------------
> 
> It should be possible to enhance current Derby RunTimeStatistics mechanism to display user specified
> optimizer directives being used if specified.

+1 to that!

Dan.


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12330020 ] 

Satheesh Bandaram commented on DERBY-573:
-----------------------------------------

It should be possible to enhance current Derby RunTimeStatistics mechanism to display user specified optimizer directives being used if specified. Since optimizer directives are typically used by application developer, not the end users, it should be easy for them to confirm the directives are taking effect. Derby provides several ways to get execution plans... including RunTimeStatistics or derby.language.logQueryPlan. These mechanisms can specify when optimizer directives are being used.


> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-573) Provider support for optimizer overrides in Derby.

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-573?page=comments#action_12331607 ] 

Daniel John Debrunner commented on DERBY-573:
---------------------------------------------

Thinking more about this DERBY-HINTS was a bad name since you are providing overrides, not hints. The generic  DERBY-PROPERTIES seems a good choice, that could support hints being added in the future by the name of the property containing the name 'hint'.

> Provider support for optimizer overrides in Derby.
> --------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.

Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-573?page=all ]

Mamta A. Satoor updated DERBY-573:
----------------------------------

    Summary: Provide support for optimizer overrides in Derby.  (was: Provider support for optimizer overrides in Derby.)

> Provide support for optimizer overrides in Derby.
> -------------------------------------------------
>
>          Key: DERBY-573
>          URL: http://issues.apache.org/jira/browse/DERBY-573
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Versions: 10.2.0.0
>     Reporter: Mamta A. Satoor
>     Assignee: Mamta A. Satoor
>  Attachments: derby573OptimierOverrides110805.txt, optimizeroverrides.html, optimizeroverrides.html
>
> Derby's query optimizer usually makes the best choice of join order and access path. The default join strategy ususally works the best too. However, there are some cases in which user may want to override the optimizer or the default values. Providing support for optimizer overrides will allow users to hand-tune the optimizer for queries. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira