Generated columns

A generated column is defined in a table where the stored value is computed using an expression, rather than being specified through an insert or update operation.

When creating a table where it is known that certain expressions or predicates will be used all the time, you can add one or more generated columns to that table. By using a generated column there is opportunity for performance improvements when querying the table data.

For example, there are two ways in which the evaluation of expressions can be costly when performance is important:

The evaluation of the expression must be done many times during a query.
The computation is complex.

To improve the performance of the query, you can define an additional column that would contain the results of the expression. Then, when issuing a query that includes the same expression, the generated column can be used directly; or, the query rewrite component of the optimizer can replace the expression with the generated column.

Where queries involve the joining of data from two or more tables, the addition of a generated column can allow the optimizer a choice of possibly better join strategies.

Generated columns will be used to improve performance of queries. As a result, generated columns will likely be added after the table has been created and populated.

Examples

The following is an example of defining a generated column on the CREATE TABLE statement:

   CREATE TABLE t1 (c1 INT,
                    c2 DOUBLE,
                    c3 DOUBLE GENERATED ALWAYS AS (c1 + c2)
                    c4 GENERATED ALWAYS AS
                       (CASE WHEN c1 > c2 THEN 1 ELSE NULL END))

After creating this table, indexes can be created using the generated columns. For example,

CREATE INDEX i1 ON t1(c4)

Queries can take advantage of the generated columns. For example,

SELECT COUNT(*) FROM t1 WHERE c1 > c2

can be written as:

 SELECT COUNT(*) FROM t1 WHERE c4 IS NOT NULL

Another example:

SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100

can be written as:

SELECT c3 FROM t1 WHERE c3 * c1 > 100