db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject How to execute optimizer overrides in a java app
Date Sat, 10 Mar 2007 01:08:30 GMT
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