Hi Ron,
A response inline...
Ronald Rudy wrote:
Interesting, I'll take a look...
In the interest of portability (in case we ever swap out Derby for something
else) I think for now I'll use a separate column I build and use specifically
for sorting.
On a somewhat related question as it pertains to indexes - if I have this
prepared statement (used frequently):
SELECT col0, col1 FROM mytable WHERE col0 = ? AND col1 = ? ORDER BY sortcol
Does it buy me anything to include the "sortcol" in the index along with "col0" and "col1"? If I should only
index "col0" and "col1", does it buy me anything to add a second index with just "sortcol" in it?
If your index includes sortcol as a trailing column, then it is what we
call a "covering index". This means that the index contains all of the
columns needed to evaluate the query. This can speed up your query
because Derby does not have to probe into the base table for extra
information. So an index like this might help you:
create index myIndex on myTable( col0, col1, sortcol );
Hope this helps,
-Rick
On Feb 5, 2010, at 9:25:00 AM, Rick Hillegas wrote:
Hi Ron,
If what you need is a custom sort order for string data, you can plug in your
own custom collator. See Knut's blog for details on how to do this:
http://blogs.sun.com/kah/entry/user_defined_collation_in_apache
You can also use generated columns to build user-defined ordering. See the
following writeup for advice on how to do this:
http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns
If these solutions don't work for you, tell us more about what you need. We may
be able to suggest another solution or at least capture your requirements for a
future enhancement.
Thanks,
-Rick
Ronald Rudy wrote:
Is there any way programmatically or otherwise to specify a java.util.Comparator type
object to sort a specific column? I realize this flies in the face of any SQL standard
or convention, but it would be really helpful to me at the moment :). And since Derby is
Java-based, I was wondering if there might be something "non-standard" I could
do to support it ....?
My alternative is to create an additional column in my database that will give
me the sorting I want (as it would be given by a custom Comparator class) using
standard varchar sorting, but this isn't preferable.
Thanks,
-Ron
