5.2.12 Branch specification for column data of variable-length data types (BRANCH) [Row store table]
When defining a table as a row store table, consider whether to specify a branch specification for column data of variable-length data types. You cannot specify a branch specification for column data of variable-length data types when defining a table as a column store table.
This subsection explains branch specifications for column data of variable-length data types. A variable-length data type means either of the following data types:
-
VARCHAR
-
VARBINARY
Normally, an entire row of data is stored on the same page. However, if a column of a variable-length data type is defined in a table, the column data of the variable-length data type might sometimes be branched and stored on separate pages.
Column data of the variable-length data type is stored according to the following rules:
-
For a column of the variable-length data type whose definition length is 255 bytes or less
The data of such a column is not branched. All the column data is stored on the same page.
-
For a column of the variable-length data type whose definition length is 256 bytes or greater
If the base row fits on one page, the data of such a column is not branched. The entire data of such a column is stored on the same page.
If the base row does not fit on one page, the data of a column of the variable-length data type is branched and stored on multiple pages.
Using the specification for BRANCH in the column definition of the CREATE TABLE statement, you can specify whether to make the data of a column of the variable-length data type always subject to branching onto separate pages. Use the following guidelines to determine whether to branch:
-
If the data of a column of the variable-length data type is not referenced frequently
Branch the data of such a column for storage on separate pages. This might improve retrieval processing performance for the table.
If you specify YES for BRANCH, the specified data of a column of the variable-length data type becomes subject to branching.
-
If the data of a column of the variable-length data type (whose data length is 256 bytes or greater) is referenced frequently
Do not branch the data of such a column. This might improve retrieval processing performance for the table.
If you specify NO for BRANCH, the specified data of a column of the variable-length data type does not become subject to branching.
If you specify the BRANCH ALL table option, all the data in a column of the variable-length data type in the table becomes subject to branching. If such column data is not referenced frequently, specify BRANCH ALL.