epoch column encoding

phil2

Hello!

I'm using data vault modeling for the data warehouse. So basically there are 3 columns in each table: entity_id, attribute_id and change_time. And because of ordering by entity_id and changes in attribute values cuming retrospectivly I eventually have epoch column as a most heavy column Is there any way to reset hidden epoch column encoding? Or make vertica to rebuild underlying containers and alter epoch for old partitions.

bryanw

Partitioning the table by change_time may help isolate the updates and potentially improve update and query performance (by partition elimination), but that isn't what you asked. Hierarchical partitions will mitigate excessive ROS count and retain good compression on older partitions, which typically see fewer changes. More on this in a bit.

AFAIK, the only way to change epoch of a row is to copy the rows - the new (copied) rows will have the current epoch. There are 2 potential strategies - both begin with CREATE TABLE <target_schema.target_table> LIKE <source_schema.source_table> INCLUDING PROJECTIONS
See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/CreateTableLike.htm

Strategy 1: copy the whole table (via INSERT /*+ direct */ ... SELECT), then atomically swap the tables (ALTER TABLE ... RENAME)
See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/RenamingTables.htm
Note: test this on non-production tables to verify primary-foreign column constraints and other attributes follow as expected; add additional ALTER statements to the process as needed.

Strategy 2: copy partitions (via INSERT /*+ direct */ ... SELECT WHERE change_time ...), then swap partitions between tables
See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/SWAP_PARTITIONS_BETWEEN_TABLES.htm
Note: swapping partition copies may be a lighter-weight (less data movement) approach to epoch maintenance, and avoids cleanup of table and column attributes
Note: Strategy 2 may not be viable in some environments where a query may race with the swap partition operation. Swapping partitions is an atomic operation as of the following hotfixes, removing this caveat:
9.2.1-3, 9.1.1-8 (at least 9.1.1.-11 recommended), 9.0.1-20

Note: if your current Vertica version is below the hotfix version listed, but Strategy 2 is desirable, please check with support for the latest recommendation regarding hotfixes and your environment (general caveat for anybody reading this message at a later date, as new recommendations may become available)