db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: How to execute optimizer overrides in a java app
Date Sun, 11 Mar 2007 03:16:26 GMT
Hi Jean,

I wonder if there is a universal way of specifying "\r" in Java like there
is for file separator. File.separator is portable on different platforms.

Mamta


On 3/10/07, Jean T. Anderson <jta@bristowhill.com> wrote:
>
> 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