Changing column groups

The mechanism of column groups allows for improved performance of partial row read operations by dividing the storage of table columns into several groups. The most commonly used scenario is to organize the storage of infrequently used attributes into a separate column group.

Creating column groups

ADD FAMILY: Creates a new group of columns in the table. The code below creates the family_small column group in the series_with_families table.

ALTER TABLE series_with_families ADD FAMILY family_small (
    DATA = "ssd",
    COMPRESSION = "off"
);

Modifying column groups

Using the ALTER COLUMN command, you can change a column group for the specified column. The code below for the release_date column in the series_with_families table changes the column group to family_small.

ALTER TABLE series_with_families ALTER COLUMN release_date SET FAMILY family_small;

The two previous commands from listings 8 and 9 can be combined into one ALTER TABLE call. The code below creates the family_small column group and sets it for the release_date column in the series_with_families table.

ALTER TABLE series_with_families
    ADD FAMILY family_small (
        DATA = "ssd",
        COMPRESSION = "off"
    ),
    ALTER COLUMN release_date SET FAMILY family_small;

Using the ALTER FAMILY command, you can change the parameters of the column group. The code below changes the storage type to hdd for the default column group in the series_with_families table:

ALTER TABLE series_with_families ALTER FAMILY default SET DATA "hdd";

You can specify any parameters of a group of columns from the CREATE TABLE command.