ClustrixDB supports Generated Columns, which are computed based on values from other columns. They are defined as part of the column’s definition and can be:
You can use generated columns to simplify queries and improve performance. If you use the same complex expression in many queries, you could encapsulate that logic as a generated column. If that expression is commonly computed, you could make the generated column STORED and take advantage of not having to compute the value each time a row is accessed, though there will be additional storage overhead.
Generated columns can be used to simulate the behavior of a materialized view or a functional index (see caveats below).
Generated columns cannot reference:
To create a table with a generated column:
sql> CREATE TABLE EMPLOYEES ( |
If no keyword is specified, a generated column is VIRTUAL and the computed value is not stored. To create a generated column that is STORED, use the STORED keyword:
sql> ALTER TABLE EMPLOYEES ADD COLUMN firstlast_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name,' ', last_name)) STORED; |
Only STORED generated columns can be part of a primary key.
Indexes on generated columns are always stored but can be created on VIRTUAL or STORED columns.
sql> CREATE INDEX fullname_i on EMPLOYEES(fullname); |
The examples below will use the following data:
sql> insert into EMPLOYEES (first_name, middle_name, last_name) values ('Jane','', 'Smith'); |
Clustrix is able to leverage indexes for generated columns that explicitly reference generated columns by name:
sql> explain select * from employees where fullname like 'smith%'; +-------------------------------------------------------------------------------------------------------------+-----------+-----------+ | Operation | Est. Cost | Est. Rows | +-------------------------------------------------------------------------------------------------------------+-----------+-----------+ | stream_combine | 212.50 | 0.51 | | filter (1.fullname >= param(3)) and (1.fullname < param(2)) and like(1.fullname, param(1), param(0)) | 211.45 | 0.51 | | index_scan 1 := EMPLOYEES.__idx_EMPLOYEES__PRIMARY | 211.33 | 2.00 | +------------------------------------------------------------------------------------------------------------------+-----------+-----------+ 3 rows in set (0.00 sec) |
But not where the same expression used by the generated column is used in the query:
sql> explain select * from employees where (CONCAT(first_name,' ',middle_name, ' ', last_name)) like '%smith'; +-------------------------------------------------------------------------------------------------------------+-----------+-----------+ | Operation | Est. Cost | Est. Rows | +-------------------------------------------------------------------------------------------------------------+-----------+-----------+ | stream_combine | 213.32 | 2.70 | | filter like(concat(1.first_name, param(3), 1.middle_name, param(2), 1.last_name), param(1), param(0)) | 212.05 | 2.70 | | index_scan 1 := EMPLOYEES.__idx_EMPLOYEES__PRIMARY | 211.99 | 3.00 | +-------------------------------------------------------------------------------------------------------------+-----------+-----------+ 3 rows in set (0.00 sec) |
ClustrixDB matches all MySQL functionality for generated columns except:
In addition, ClustrxDB has the following caveats: