Currently I have a table (A) with column which store some aggregated values from other table (B). On change data in the B table, this column updated accordingly, through ordinary UPDATE A ... FROM SELECT B statement.
Vertica 8.1 provides relevant feature: Flattened Tables. With SET USING constraint it looks like the same process: on demand (by call the REFRESH_COLUMNS()) read the source table and write results to the target column.
Documentation says about the benefits of Flattened Tables too briefly:
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.
Yes, this constraint looks cleaner than manual update. But I just want to understand: is it only "syntax sugar" with the same operations under the hood or completely different way? Does it have any advantages or disadvantages in performance? May be someone already use it and can share own experiences.
I documented the flattened tables feature, but that was awhile back, so I checked in with the lead developer to refresh my memory. His answer:
Yes, in some respects, flattened tables are somewhat akin to "syntax sugar". However, it does have a few optimizations that set it apart. For example, you can refresh columns with SET USING constraints in UPDATE mode, which avoids modifying unchanged rows. Also, there's no DML (at least not in Vertica) that lets you refresh multiple columns at same time.