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) Provider support for optimizer overrides in Derby.
Date Wed, 09 Nov 2005 07:41:23 GMT
     [ 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


Mime
View raw message