[ 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
