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.
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.
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))
CREATE INDEX i1 ON t1(c4)
SELECT COUNT(*) FROM t1 WHERE c1 > c2
can
be written as: SELECT COUNT(*) FROM t1 WHERE c4 IS NOT NULL
SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100
can
be written as:SELECT c3 FROM t1 WHERE c3 * c1 > 100