db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Perhaps a stupid question
Date Fri, 05 Feb 2010 14:52:56 GMT
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,
> 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

View raw message