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?
TIA
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
application.
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,
-Rick
