Merge Tables

Horizontally partitioned tables are defined with the CREATE MERGE table syntax.

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 ]

Table elements are described in table-elements.

A merge table logically combines the data of multiple other tables (called partition tables which are added via ALTER TABLE merge_table ADD TABLE partition_table) which all must have the exact same table definition. This merge table is easier to extend/change with new partitions than a view which combines multiple SELECT queries (via UNION ALL) and can processes queries faster. Also with PARTITION BY specified, the virtual merge table becomes updatable, so allow inserts, updates, deletes and truncate on the merge table directly instead of the partition tables.

The added partition tables must have the same column definitions and layout as the merge table. Only user defined tables and merge tables can be added to a merge table set.
When the merge table was created with a PARTITION BY clause, the AS PARTITION clause must be specified when adding a partition table.