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 Satoor <ms...@gmail.com> on 2005/11/09 08:41:20 UTC

[PATCH](DERBY-573) Provide support for optimizer overrides in Derby

Hi Everyone,
 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?
thanks,
Mamta

Re: [PATCH](DERBY-573) Provide support for optimizer overrides in Derby

Posted by Kathey Marsden <km...@sbcglobal.net>.

Mamta Satoor wrote:

>Hi Satheesh,
>
>Thanks for reviewing the patch.
>
>I think your concern is well founded. A new column in a system table
>referenced by metadata.properties would cause a problem in soft upgrade
>mode. I am not sure if we have run into this in the past and how we handled
>it.
>
With an older version of Cloudscape a few years ago we had a similar
issue where a metadata query referenced a new stored procedure which
didn't exist on an older version and to this day we still struggle with
that unfortunate event because there are errors recompiling stored
prepared statements when reverting   to the previous version.  After
that we changed the Cloudscape code to drop the metadata stored prepared
statements whenever the version changed up or down and let them get
recreated with the new version.  That does not occur with Derby however.

My feeling is that it would be safest in the long run to always drop the
metadata statements whenever the version changes up or down and let them
be recreated  with the current version.   Too late for  existing
versions but a safe  long term strategy.

Just my 2 cents.

Kathey