db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-573) Provide support for optimizer overrides in Derby.
Date Wed, 07 Dec 2005 07:28:09 GMT
     [ 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
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:
>     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:
For more information on JIRA, see:

View raw message