11.1.11 Changing a row store table to a column store table

To change a row store table to a column store table, you must first delete the row store table, and then redefine the table as a column store table.

Important

Note that some restrictions that are not placed on row store tables are placed on column store tables. Therefore, before you change a row store table to a column store table, check the restrictions on column store tables. For details about the restrictions on column store tables, see (1) Restrictions on column store tables in 5.2.2 Criteria for selecting row store tables and column store tables.

Note

For details about how to change a column store table to a row store table, see 11.1.12 Changing a column store table to a row store table.

Change a row store table to a column store table by using the following procedure:

Procedure

1. Output all data that is stored in the row store table.

   Use the adbexport command to output all data that is stored in the row store table to a file.

   If the row store table is a multi-chunk table, note the following points when executing the adbexport command:

   • If the status and configuration of the chunks in the multi-chunk table do not need to be retained

     No notes apply. Output all data that is stored in the row store table to a file.

   • If the status and configuration of the chunks in the multi-chunk table need to be retained

     You must output all data from the row store table to a file in units of chunks. Unless you output data in units of chunks, the status and configuration of the chunks will not be retained in the column store table that will store the data. Therefore, you must perform an export for each chunk.

     For details about outputting data for each chunk, see 11.4.5 Exporting data in units of chunks.

2. Check the definition information of the row store table.

   Check the specification content of the CREATE TABLE statement that was used to define the row store table. You will use the specification content when you define the column store table.

   If you are not sure of the specification content of the CREATE TABLE statement that was used to define the row store table, see (28) Finding out base table definition information in B.22 Searching a dictionary table. By retrieving data from a dictionary table, you can check the specification content of the CREATE TABLE statement that was used to define the row store table.

3. Check the definition information of the indexes defined for the row store table.

   If indexes have been defined for the row store table, check the specification content of the CREATE INDEX statement that was used to define the indexes. You will use the specification content when you define indexes for the column store table.

   If you are not sure of the specification content of the CREATE INDEX statement that was used to define the indexes, see (29) Finding out index definition information in B.22 Searching a dictionary table. By retrieving data from a dictionary table, you can check the specification content of the CREATE INDEX statement that was used to define the indexes.

4. Delete the row store table.

   Use the DROP TABLE statement without specifying drop-behavior to delete the row store table.

5. Define a column store table.

   Use the CREATE TABLE statement to define a column store table. Note the following points when you create a CREATE TABLE statement:

   • Create a CREATE TABLE statement with the specification content of the CREATE TABLE statement that was used to define the row store table (the specification content you checked in step 2).

   • Specify COLUMN for STORAGE FORMAT in the CREATE TABLE statement.

6. Define indexes for the column store table.

   If the row store table that you deleted had indexes defined, use the CREATE INDEX statement to define indexes for the column store table. Note the following points when you create a CREATE INDEX statement:

   • Create a CREATE INDEX statement with the specification content of the CREATE INDEX statement that was used to define the indexes (the specification content you checked in step 3).

     Note that if a B-tree index was defined for the row store table, we recommend that you do not define a B-tree index for the column store table. For details, see (3) B-tree indexes used when retrieving data from column store tables in 5.2.2 Criteria for selecting row store tables and column store tables.

   • You cannot define a text index for column store tables.

7. Store data in the column store table.

   Use the adbimport command to store all of the data that was output in step 1 to the column store table defined in step 5.

   If the column store table is a multi-chunk table, note the following points when executing the adbimport command:

   • If the status and configuration of the chunks in the multi-chunk table do not need to be retained

     Do not specify the -d or -b option for the adbimport command.

   • If the status and configuration of the chunks in the multi-chunk table need to be retained

     You must import for each chunk the data that was output in units of chunks in step 1.

     When you perform the first data import, execute the adbimport command without specifying the -d or -b option.

     When you perform the second and following data imports, use the background import method. Execute the adbimport command by specifying the -b option.

     When you perform the last data import, import the data stored in the chunk that was the current chunk in step 1 by using the background import method. Execute the adbimport command by specifying the -b option.

8. Re-validate viewed tables.

   The viewed tables whose underlying table is the row store table that was deleted in step 4 are invalidated. Therefore, after defining a column store table, you must re-validate the viewed tables. When you re-validate the viewed tables, see (4) When viewed tables are invalidated due to erroneous deletion of a table in 11.2.8 Releasing a viewed table from invalidation.

The procedure for changing a row store table to a column store table is complete.

Note

• For details about the adbexport and adbimport commands, see the manual HADB Command Reference.

• For details about the DROP TABLE, CREATE TABLE, and CREATE INDEX statements, see the manual HADB SQL Reference.

To Page Top