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 Sat, 10 Mar 2007 05:01:40 GMT
Jean, what you are experiencing is the expected behavior.

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