Merge Tables

A merge table is a horizontally partitioned virtual table comprising multiple physical tables referred to as partition tables. Partitioning is a database design technique whereby a large dataset is divided into smaller constituents with the intent of improving query performance. A table can be partitioned vertically or horizontally, with the latter option constituting a row-wise splitting of the table in question. This form of partitioning is the basis of merge tables and is generally applicable to tables with substantially large numbers of rows.

The syntax of the CREATE MERGE TABLE statement, which creates an empty merge table, is as follows:

table-definition:
     CREATE MERGE TABLE [ IF NOT EXISTS ] qname  table_content_source
        [ PARTITION BY { RANGE | VALUES } { ON '(' column_name ')' |  USING '(' query_expression ')' } ]

table_content_source:
     '(' table_element_list ')'
   | [ '(' column_name_list ')' ]  AS select_query  [ WITH DATA | WITH NO DATA ]

table_element_list:
     table_element
   | table_element_list ',' table_element

alter_merge_tables:
     ALTER TABLE [ IF EXISTS ] qname ADD  TABLE qname [ AS PARTITION partition_spec ]
   | ALTER TABLE [ IF EXISTS ] qname SET  TABLE qname   AS PARTITION partition_spec
   | ALTER TABLE [ IF EXISTS ] qname DROP TABLE qname [ RESTRICT | CASCADE ]

partition_spec:
     IN '(' query_expression [',' query_expression [...] ] ')' [ WITH NULL VALUES ]
   | FROM partition_range_from TO partition_range_to [ WITH NULL VALUES ]
   | FOR NULL VALUES

partition_range_from:
     RANGE MINVALUE
   | query_expression

partition_range_to:
     RANGE MAXVALUE
   | query_expression

query_expression:
     select_no_parens [ order_by_clause ] [ limit_clause ] [ offset_clause ] [ sample_clause ]

For a complete definition of the above syntax, proceed to Table Elements.

To add a partition table to an existing merge table, under condition of equivalent column definitions, execute the following:

ALTER TABLE merge_table ADD TABLE partition_table;

The same table structure can be attained by creating a view comprising multiple SELECT queries combined via UNION ALL operations. However, among the benefits of using merge tables instead of views are seamless extensibility, as demonstrated by the previous ALTER TABLE statement, and faster query execution times.

To run INSERT, UPDATE, DELETE, and TRUNCATE statements directly on a merge table, it is necessary to include a PARTITION BY clause in the initial CREATE MERGE TABLE statement. Otherwise, these statements can only be performed on the partition tables that comprise the merge table. Furthermore, an ALTER TABLE statement will require an AS PARTITION clause to be specified in order to add new partitions to the merge table. Only user-defined tables can be added to a merge table.

The following blog post from the MonetDB team provides further insight, along with some examples: Updatable Merge Tables.