COLUMNGROUPS

A column group is a set of columns that are treated as a single unit for query performance.

Column groups work only when the extended statistics feature is enabled in the target data warehouse. By gathering statistics on a column group, the optimizer can more accurately estimate cardinality when a query groups these columns together.

Note:

• For a dataset, a column can belong to multiple column groups.
• Different column groups with the same columns in same orders aren't allowed.
• Different column groups with the same columns in different orders are allowed.

Syntax

column_group_block ::= COLUMNGROUPS '[' column_group_statement ... ']'
column_group_statement ::= CREATE COLUMNGROUP identifier ON table_name column_list ;

Example

COLUMNGROUPS
[
CREATE COLUMNGROUP FAW_DW_SALES ON DW_SALES[CUST_ID, SALE_ID];
CREATE COLUMNGROUP FAW_DW_SALES2 ON DW_SALES[SALE_ID, CUST_ID];
CREATE COLUMNGROUP FAW_DW_CUSTOMER ON DW_CUSTOMER[CUST_ID, CUST_NAME];
]