Derby 10.5 preview: Generated Columns

In the Apache Derby community we're currently working on a new feature release (version 10.5) which we hope to have ready very soon now. While we're waiting for it to be properly tested and approved, I'll give a sneak preview of some of the features that we've added since 10.4. In this entry, I'll take a look at generated columns.

A generated column is a column whose value is defined as an expression. The expression may refer to other columns in the same table, and the value of a generated column will be automatically updated if any of the columns on which it depends are modified.

Let's look at a simple example. The statement below will create a table with three integer columns, where the last one is automatically generated as the sum of the first two columns.

ij> CREATE TABLE T(COL1 INT, COL2 INT, COL3 GENERATED ALWAYS AS (COL1+COL2));
0 rows inserted/updated/deleted

Now insert a couple of rows, and see that the value of the third column is calculated automatically:

ij> INSERT INTO T (COL1, COL2) VALUES (1,2), (3,4), (5,6);
3 rows inserted/updated/deleted
ij> SELECT \* FROM T;
COL1       |COL2       |COL3       
-----------------------------------
1          |2          |3          
3          |4          |7          
5          |6          |11         

3 rows selected

Also, any updates of COL1 or COL2 will immediately trigger an update of COL3:

ij> UPDATE T SET COL2 = 100 WHERE COL1 = 1;
1 row inserted/updated/deleted
ij> UPDATE T SET COL1 = NULL WHERE COL1 = 5;
1 row inserted/updated/deleted
ij> SELECT \* FROM T;
COL1       |COL2       |COL3       
-----------------------------------
1          |100        |101        
3          |4          |7          
NULL       |6          |NULL       

3 rows selected

A somewhat more useful use case for generated columns is case-insensitive search. Traditionally, this has been solved by converting the column in question to upper case on the fly in the select statement. For example like this to find all the values starting with a 'c' regardless of case: SELECT ... WHERE UPPER(WORD) LIKE 'C%'

The traditional approach has some disadvantages:

  • The upper case values must be regenerated each time the query is performed
  • Derby doesn't currently let you create an index on an expression (like UPPER(WORD)), so the query must always go through the entire table to find the rows you are looking for

With generated columns, the upper case values are only generated once when the value is inserted or modified. You're also allowed to create an index on a generated column to speed up queries that access it. (Yes, you could achieve the same by using insert/update triggers to keep an ordinary, indexed column updated, but it's a lot easier with generated columns, and insert/update performance should also be better.) To make it possible to perform an efficient case-insensitive search on a column, add a generated column which contains an upper case copy of it and create an index on that column, as shown in the code below:

ij> CREATE TABLE WORDS(WORD VARCHAR(20), UWORD GENERATED ALWAYS AS (UPPER(WORD)));
0 rows inserted/updated/deleted
ij> CREATE INDEX IDX_UWORD ON WORDS(UWORD);
0 rows inserted/updated/deleted
ij> INSERT INTO WORDS(WORD) VALUES 'chocolate', 'Coca-Cola', 'hamburger', 'carrot';
4 rows inserted/updated/deleted
ij> SELECT WORD FROM WORDS WHERE UWORD LIKE 'C%';
WORD                
--------------------
chocolate           
Coca-Cola           
carrot              

3 rows selected

If you want to try it out yourself, you can download the latest Derby 10.5 release candidate (RC2 at the time of writing this).

Comments:

I still think that there should be a better fix for case insensitive queries. Having to duplicate all columns that contain text will just bloat a database too much.

Posted by Pete saat on April 17, 2009 at 02:34 AM CEST #

Very interesting,but: why is it better than stored procedures ? and how the language to create the expression will be defined ? is it will foolow some standard ?

otherwise, I believe the solution will be too much proprietary and locked to Derby..

it is cool, for sure, but also an avoidable feature for the project looking for portability and following SQl standards.

Posted by Felipe Gaúcho on April 17, 2009 at 07:42 AM CEST #

Pete: I agree, this isn't a complete solution for case-insensitive search, but may help in some use cases. If you want your entire database to be case-insentive, the collator-based solution described in one of my previous blog entries (http://blogs.sun.com/kah/entry/user_defined_collation_in_apache) may be better suited, though it has its limitations too (currently no possibility to specify collation per table/column, and indexes aren't used to optimize LIKE queries in databases with non-default collation).

Felipe: This feature is part of the SQL standard (feature T175 in SQL 2003) so it shouldn't lock you to Derby in any way.

Posted by Knut Anders Hatlen on April 17, 2009 at 08:23 AM CEST #

Post a Comment:
Comments are closed for this entry.
About

kah

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today