Flattened Tables
Highly normalized database design often uses a star or snowflake schema model, comprising multiple large fact tables and many smaller dimension tables. Queries typically involve joins between a large fact table and multiple dimension tables. Depending on the number of tables and quantity of data that are joined, these queries can incur significant overhead.
To avoid this problem, some users create wide tables that combine all fact and dimension table columns that their queries require. These tables can dramatically speed up query execution. However, maintaining redundant sets of normalized and denormalized data has its own administrative costs.
Denormalized, or flattened, tables, can minimize these problems. Flattened tables can include columns that get their values by querying other tables. Operations on the source tables and flattened table are decoupled; changes in one are not automatically propagated to the other. This minimizes the overhead that is otherwise typical of denormalized tables.
A flattened table defines derived columns with one of the following column constraint clauses:
DEFAULT query-expression
sets the column value on two events:- When the column is created with
CREATE TABLE
orALTER TABLE…ADD COLUMN
. - Any table load operation such as
INSERT
. SET USING query-expression
sets the column value only when the functionREFRESH_COLUMNS
is invoked.
In both cases, query-expression must return only one row and column value, or none. If the query returns no rows, the column value is set to NULL
.
Like other tables defined in Vertica, you can add and remove DEFAULT
and SET USING
columns from a flattened table at any time. Vertica enforces dependencies between a flattened table and the tables that it queries. For details, see Modifying SET USING and DEFAULT Columns.