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: Function-based indexes
Date Tue, 14 Aug 2007 13:51:11 GMT
Williamson, Nick wrote:
> Hi all,
> In Oracle, it's possible to have an index column that is the result of a
> function, for example:
> CREATE INDEX index_name ON table_name
>   (
>     UPPER("column_name")
>   );
> It doesn't seem possible to do this in Derby. What would be the best way
> to mimic this behaviour? I guess I would need a new column to hold the
> uppercased value of the original column, but can I get Derby to maintain
> the new column for me, perhaps with a trigger? Or is it possible to have
> a column that is itself the result of a function on another column?
> Nick
Hi Nick,

There are two related JIRAs for this missing functionality: DERBY-455 
(function indexes) and DERBY-481 (computed columns). In my opinion, 
DERBY-481 is the easier of the two to implement. It has become even more 
attractive now that the new 10.3 release lets you drop columns--this 
means that you can drop and recreate computed columns as you debug your 

As you say, you can use Triggers to hand-roll your own computed columns. 
This won't perform as well as a good solution to DERBY-481 (but I don't 
have any performance figures to support that opinion). It's also a 
little brittle: your computed column's value will depend on the contents 
of other input columns in the row and you will have to make sure that 
your Trigger is sensitive to changes in all of those input columns.

Another solution would be to encapsulate your writes in methods which 
pre-calculate the computed result before stuffing the columns. This is a 
little scary because it's hard to prove to yourself that unauthorized 
writes aren't leaking into your database from dark corners of your 
application. You could regain that confidence by adding a table-level 
Check constraint--that Check constraint would verify that your computed 
column has the correct value. Again, this won't perform as well as a 
good solution to DERBY-481. The Check constraint will be fired every 
time the row is altered rather than just when the input columns change.

Hope this helps,

View raw message