Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 13762 invoked from network); 6 Dec 2005 18:44:21 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 6 Dec 2005 18:44:21 -0000 Received: (qmail 923 invoked by uid 500); 6 Dec 2005 18:44:20 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 879 invoked by uid 500); 6 Dec 2005 18:44:20 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 858 invoked by uid 99); 6 Dec 2005 18:44:19 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Dec 2005 10:44:19 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: domain of msatoor@gmail.com designates 64.233.184.198 as permitted sender) Received: from [64.233.184.198] (HELO wproxy.gmail.com) (64.233.184.198) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Dec 2005 10:44:18 -0800 Received: by wproxy.gmail.com with SMTP id 55so487003wri for ; Tue, 06 Dec 2005 10:43:58 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=np650NP4Zr6Exg3vzeyxlsEjRZd7fqEVK1dGnuv/EV0cNi6YaML9060r8AI6fD8zKykI3PiVTXkRUjJFea2WOJ1lUqzc+z8wOcofnoXI0dfMg3cKR4MEuTN5nFgMbqvmRvtIj90aSapNPmbDnYOgM11prZcE9cWymQPPmKBf32c= Received: by 10.54.138.5 with SMTP id l5mr916796wrd; Tue, 06 Dec 2005 10:43:58 -0800 (PST) Received: by 10.54.133.2 with HTTP; Tue, 6 Dec 2005 10:43:58 -0800 (PST) Message-ID: Date: Tue, 6 Dec 2005 10:43:58 -0800 From: Mamta Satoor To: derby-dev@db.apache.org Subject: Re: [PATCH](DERBY-573) Provide support for optimizer overrides in Derby In-Reply-To: <4395D67D.7050809@Sourcery.Org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10479_18278792.1133894638112" References: <4395D67D.7050809@Sourcery.Org> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_10479_18278792.1133894638112 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks, Satheesh, for the update. But I am very close to submitting a new patch for this functionality which will also include soft and hard upgrade to 10.2 So, give me a day or two to submit the new patch and then you (and anyone else who might be interested) can review that patch. thanks, Mamta On 12/6/05, Satheesh Bandaram wrote: > > Hi Mamta, > > I will review with the goal of committing this patch this week. THANKS fo= r > being patient... I was held up trying to get my GrantRevoke Part I patch = out > last few weeks. > > Satheesh > > Mamta Satoor wrote: > > Hi Everyone, > > I posted this patch about 20days back. Does anyone have any feedback on > the patch? Is it good to go? > > thanks, > Mamta > > > On 11/8/05, Mamta Satoor wrote: > > > > Hi Everyone, > > > > I have the patch for optimizer overrides support in Derby. Alongwith th= e > > 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 engin= e > > 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 t= hat, > > it looks for propertyName=3Dvalue pairs on that same comment line in pa= rser'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 remaini= ng > > 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\db2Compat= ibility.sql > > M > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\copyfiles= .ant > > A > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\optimizer= Overrides.sql > > M > > java\testing\org\apache\derbyTesting\functionTests\tests\lang\checkCons= traint.sql > > M > > java\testing\org\apache\derbyTesting\functionTests\tests\store\access.s= ql > > A > > java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\opti= mizerOverrides.out > > M > > java\testing\org\apache\derbyTesting\functionTests\master\db2Compatibil= ity.out > > A > > java\testing\org\apache\derbyTesting\functionTests\master\optimizerOver= rides.out > > > > M > > java\testing\org\apache\derbyTesting\functionTests\master\checkConstrai= nt.out > > M > > java\testing\org\apache\derbyTesting\functionTests\master\access.out > > M > > java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.run= all > > M > > java\testing\org\apache\derbyTesting\functionTests\suites\derbynetmats.= runall > > > > Comments/questions on the patch? > > thanks, > > Mamta > > > > ------=_Part_10479_18278792.1133894638112 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Thanks, Satheesh, for the update. But I am very close to submitting a = new patch for this functionality which will also include soft and hard upgr= ade to 10.2 So, give me a day or two to submit the new patch and then you (= and anyone else who might be interested) can review that patch.
 
thanks,
Mamta

 
On 12/6/05, = Satheesh Bandaram <satheesh= @sourcery.org> wrote:
Hi Mamta,

I will review w= ith the goal of committing this patch this week. THANKS for being patient..= . I was held up trying to get my GrantRevoke Part I patch out last few week= s.

Satheesh=20


Mamta Satoor wro= te:=20
Hi Everyone,
 
I posted this patch about 20days back. Does anyone have any feedback o= n the patch? Is it good to go?
 
thanks,
Mamta

 
On 11/8/05, = Mamta Satoor <msatoor@gmail.com= > wrote: =20
Hi Everyone,
 
I have the patch for optimi= zer overrides support in Derby. Alongwith the patc= h, I have attached the updated functional spec to the JIRA= entry Derby-573.=20
 
Majority of the changes went into the sqlgrammar.jj because Derby engi= ne 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&nb= sp;parser now looks for character sequence -- DERBY-PROPERTIES (case insens= itive and space between -- and D is optional) and once it finds that, it lo= oks for propertyName=3Dvalue pairs on that same comment line in parser's pr= opertyList method. The parser does the basic check to make sure that the sa= me property is not used more than once for a given table. The remaining che= cks on the properties like checking the existence of user specified in= dex etc are done in the bind phase.=20
 
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 co= ncatenated with the next line of the sql.
 
Import.java had to be changed to user --DERBY-PROPERTIES rather than P= ROPERTIES.
 
Added a new test optimizerOverrides.sql which runs in both embedded an= d network server mode.
 
Rerunning all the tests after syncing the codeline to make sure nothin= g 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 statis= tics so that user can verify that the optimizer ov= errides are getting used.
 
I haven't researched into upgrade much but will the changes in me= tadata.properties require some upgrade path for existing databases? Any poi= nters here will be very useful.
 
svn stat
M      java\engine\org\apache\derby\impl\load= \Import.java
M      java\engine\org\apache\derb= y\impl\sql\compile\sqlgrammar.jj
M      java\en= gine\org\apache\derby\impl\jdbc\metadata.properties
M   &= nbsp;  java\engine\org\apache\derby\iapi\reference\SQLState.java=20
M      java\engine\org\apache\derby\loc\messag= es_en.properties
M      java\testing\org\apache= \derbyTesting\functionTests\tests\lang\db2Compatibility.sql
M  = ;    java\testing\org\apache\derbyTesting\functionTests\test= s\lang\copyfiles.ant=20
A      java\testing\org\apache\derbyTesting\fu= nctionTests\tests\lang\optimizerOverrides.sql
M    &= nbsp; java\testing\org\apache\derbyTesting\functionTests\tests\lang\checkCo= nstraint.sql
M      java\testing\org\apache\der= byTesting\functionTests\tests\store\access.sql=20
A      java\testing\org\apache\derbyTesting\f= unctionTests\master\DerbyNet\optimizerOverrides.out
M   &= nbsp;  java\testing\org\apache\derbyTesting\functionTests\master\db2Co= mpatibility.out
A      java\testing\org\apache\= derbyTesting\functionTests\master\optimizerOverrides.out=20
M      java\testing\org\apache\derbyTesting\fu= nctionTests\master\checkConstraint.out
M      j= ava\testing\org\apache\derbyTesting\functionTests\master\access.out
M&nb= sp;     java\testing\org\apache\derbyTesting\functionTe= sts\suites\derbylang.runall=20
M      java\testing\org\apache\derbyTesting\f= unctionTests\suites\derbynetmats.runall
 
Comments/questions on the patch?
thanks,
Mamta


------=_Part_10479_18278792.1133894638112--