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.

Changing storage type

The code below changes the storage type to rot for the default column group in the series_with_families table:

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

Changing compression codec

The code below changes the compression codec to lz4 for the default column group in the series_with_families table:

ALTER TABLE series_with_families ALTER FAMILY default SET COMPRESSION "lz4";

Changing cache mode

When switching the cache mode to in_memory for an existing table with ALTER TABLE, the pages that are not yet in memory are loaded automatically.

If the table was previously in in_memory mode and ALTER TABLE then sets the cache mode to regular, the pages that are already in memory stay there, but they may later be evicted according to the general caching policy.

The code below changes the cache mode to in_memory for the default column group in the series_with_families table:

ALTER TABLE series_with_families ALTER FAMILY default SET CACHE_MODE "in_memory";

You can specify any column group parameters described in the CREATE TABLE command.