11.1.12 Changing a column store table to a row store table

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

Important: Note that the number of items that can be specified during table definition for a row store table is larger than for a column store table. Therefore, there are some points you must note before a change to a row store table. For details, see 5.2.1 Flow of table design.

Note: For details about how to change a row store table to a column store table, see 11.1.11 Changing a row store table to a column store table.

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

Procedure

Output all data that is stored in the column store table.

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

If the column 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 column 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 column 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 row 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.
Check the definition information of the column store table.

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

If you are not sure of the specification content of the CREATE TABLE statement that was used to define the column 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 column store table.
Check the definition information of the indexes defined for the column store table.

If indexes have been defined for the column 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 row 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.
Delete the column store table.

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

Use the CREATE TABLE statement to define a row 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 column store table (the specification content you checked in step 2).
- Specify ROW for STORAGE FORMAT in the CREATE TABLE statement.
Define indexes for the row store table.

If the column store table that you deleted had indexes defined, use the CREATE INDEX statement to define indexes for the row 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).
- When you create a CREATE INDEX statement, remember that you might need to define B-tree indexes and text indexes if necessary.
  
  For column store tables, it is not preferable to define B-tree indexes. It is impossible to define text indexes. Because only range indexes are defined in normal cases, keep this in mind when you create a CREATE INDEX statement.
Store data in the row store table.

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

If the row 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.
Re-validate viewed tables.

The viewed tables whose underlying table is the column store table that was deleted in step 4 are invalidated. Therefore, after defining a row 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 column store table to a row 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