db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: How to execute optimizer overrides in a java app
Date Sat, 10 Mar 2007 15:45:24 GMT
Mamta Satoor wrote:
> Jean, what you are experiencing is the expected behavior.

Hi, Mamta,

Yes -- sorry I should have been more clear in my post. It's clearly
documented that the derby-properties clause must come in the right place
(ie. after the table name to specify the index) and that it must be at
the end of the line.

In my post I should have said "I also showed two queries that are
expected to get syntax errors." --I'm not sure that sample invalid
syntax needs to be put into the docs.

Is there any feedback on using the "\r" ? In the back of my mind I'm
thinking that won't be portable between Windows and Unix.

 -jean

> In Derby, everything after the comment delimiter which is -- is
> considered a
> comment and not part of the sql. Optimizer override is just a special
> comment with it's own syntax rule. And the syntax rule for optimizer
> overrides expects propertyname=value.
> 
> When a user specifies --derby-properties index=IDX1 order by num, Derby
> determines that is an optimizer override. index=IDX1 follows the syntax
> rule
> of optimizer override but the string "order by num" and hence the user will
> see a syntax error.
> 
> A Jira issue for java example in the docs would be great. If you have any
> other feedback on the optimizer override doc page, please post that too.
> 
> Thanks,
> Mamta
> 
> On 3/9/07, Jean T. Anderson <jta@bristowhill.com> wrote:
> 
>>
>> I got a question today on how to do optimizer overrides in a java app
>> and thought I would post my answer here to verify what I said was
>> correct and see if anyone has better ideas on how to do it. The docs [1]
>> have good examples that can be run from ij, but it wasn't clear to the
>> user how to execute them in a java app. The main sticking poing was the
>> optimizer override must be in a comment at the end of the line.
>>
>> I modified the SimpleApp.java that comes with the Derby distribution to
>> add two indexes:
>>
>>   s.execute("create index IDX1 on derbyDB(num)");
>>   s.execute("create index IDX2 on derbyDB(addr)");
>>
>> I showed two queries that work (notice how there must be a carriage
>> return after the comment in the second query):
>>
>>   ResultSet rs = s.executeQuery(
>>   "SELECT num, addr FROM derbyDB --derby-properties index=IDX1");
>>
>>   ResultSet rs = s.executeQuery(
>>   "SELECT num, addr FROM derbyDB --derby-properties index=IDX1 \r order
>> by num");
>>
>> The "\r" worked, but is there a better way to do this?
>>
>> I also showed two queries that get syntax errors.
>>
>> This query gets an error because there isn't a carriage return after the
>> --derby-properties clause and before the "order by" clause:
>>
>>   ResultSet rs = s.executeQuery(
>>      "SELECT num, addr FROM derbyDB --derby-properties index=IDX1 order
>> by num");
>>
>> This query gets a syntax error because the --derby-properties clause
>> must come after the table name:
>>
>>   ResultSet rs = s.executeQuery(
>>      "SELECT num, addr FROM derbyDB order by num --derby-properties
>> index=IDX1");
>>
>> Any suggestions/corrections? I'll open a Jira issue with a java example
>> to add to the existing docs.
>>
>> thanks,
>>
>> -jean
>>
>> [1] http://db.apache.org/derby/docs/dev/tuning/ctunoptimzoverride.html
>>
> 


Mime
View raw message