Neoview SQL Reference Manual (R2.5)

Limits for Tables
You can create tables and indexes with rows that are 32,708 bytes in size and blocks that are 32K
bytes in size. The default block size for all create table and index operations is 32768 (32K) byte
block size (in all cases).
No tools are provided for explicitly migrating an existing 4KB block size table to a 32KB block
size table. If you want a larger block size, a new table must be created and data copied from the
old table to the new table. Table 2-2 describes the block size, the size available to Neoview SQL,
and the size available to the user.
Table 2-2 Maximum Row Sizes Available
Max row size available to usersMax row size available to SQLBlock size
403640404096
327083271232768
Tables and Triggers
If you plan to create triggers on a table, its primary key length cannot exceed 2032 bytes. A table
which will not have triggers can have a primary key of 2048 bytes. For details about this limit,
see “Triggers and Primary Keys” (page 99).
Calculating Row Size
The row size on disk depends upon the following:
• Size of columns based on type.
• Number of variable length columns.
• Number of nullable columns.
• Padding bytes for proper alignment.
• Row header.
A set of 32 nullable columns require an additional 4 bytes. Each variable length column requires
an additional 4 bytes. Though columns are rearranged to reduce the number of padding bytes,
up to 9 additional bytes might be required to ensure proper column alignment for performance
reasons.
A rough estimate can be calculated using the following formula. The actual size of the row may
vary.
Row Size in bytes = Row Header (4) +
((Number of nullable columns / 32 + 1) * 4) +
(Number of variable columns * 4) +
Sum of sizes of all columns based on type.
Creating Partitions Automatically
When creating a table, users can specify that the table is not partitioned using the NO PARTITION
clause. The default for the table is to be partitioned.
You may also specify the MAX TABLE SIZE clause that is used to allocate space on the disk. It
is advisable that you specify a value, even if it is approximate, because it helps to allocate the
appropriate amount of space for the table. If this clause is not specified, Neoview SQL will decide
how to allocate the space for the table.
If the table is partitioned then the table is automatically partitioned across all the disk volumes
if the system has less than or equal to 256 disks. If the system has more than 256 disks, then the
table is partitioned across half the disks on the system.
CREATE TABLE Statement 87