Merge Tables

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

table-definition:
   CREATE MERGE TABLE [ IF NOT EXISTS ]  table_name  table_content_source
 | CREATE MERGE TABLE [ IF NOT EXISTS ] [ schema_name . ] table_name
    ( column_definition(s)_and_optional_table-constraints_list )
    [ PARTITION BY { RANGE | VALUES }  { ON ( column_name )  |  USING ( 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-statement:
   ALTER TABLE [ IF EXISTS ] [ schema_name . ] merge_table_name 
    ADD TABLE [ schema_name . ] table_name [ AS PARTITION  partition_spec ] 
 | ALTER TABLE table_name DROP [ TABLE ] table_name

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.

Limitations: The added table 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. The AS PARTITION option is supported from release Apr2019 (11.33.3) onwards. The IF EXISTS option is supported from release Apr2019 (11.33.3) onwards.

alter-partitions:
  ALTER TABLE [ IF EXISTS ] [ schema_name . ] merge_table_name
   SET TABLE [ schema_name . ] table_name
   AS PARTITION  partition_spec

partition_spec:
  IN ( expression [ , expression ] [, ... ] )  [ WITH NULL VALUES ]
| FROM  { RANGE MINVALUE | expression }  TO  { RANGE MAXVALUE | expression }  [ WITH NULL VALUES ]
| FOR NULL VALUES

merge-action:
  [ WITH cte_alias AS [SELECT_query] [, cte_alias AS SELECT query] [, ... ] ]
  MERGE INTO [ schema_name . ] table_name [ [ AS ] target_alias ]
  USING table_or_cte_ref [ [ AS ] source_alias ]
    ON merge_search_condition
  WHEN merge_action
  [ WHEN merge_action ]

merge_action:
  NOT MATCHED THEN INSERT [ (column1 [, column2 ...] ) ] [ { VALUES (value1 [, value2 ...] ) | DEFAULT VALUES } ]
| MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ] [, ... ]
| MATCHED THEN DELETE