SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-16
Additional Guidelines for Creating Tables
Thus, the actual number of rows stored in a block might be greater than your
calculated value of N if VARCHAR columns use fewer bytes than their maximum byte
length.
Although the size of a VARCHAR column can change, the total row length cannot
exceed the specified maximum record size for the table.
Moreover, if applications will update and insert records, you will want to leave free
space in the block to avoid too-frequent block splits, which eventually fragment the file,
use unnecessary storage space, and, possibly, affect performance. The SLACK,
ISLACK, and DSLACK options in the SQLCI LOAD command and in the online FUP
RELOAD command allow you to specify the amount of free space that will be left in
each block when records are loaded into the table. For more information about these
options, see Reorganizing a Database Online on page 8-2 and Loading, Copying,
Appending, and Purging Data on page 8-7.
Entry-Sequenced and Relative Tables
The maximum record size for an entry-sequenced or relative table is the block size
less 22 bytes for block header information. In addition, each record in a block requires
two bytes to store the record’s offset location from the block header. Thus, for the
maximum block size of 4096, the maximum usable record size is 4072 bytes if you
store one record per block.
To determine the maximum number of records that will fit in each block (for an entry-
sequenced table) or fill each block (for a relative table), use this formula, in which N is
the number of records, B is the block size, and R is the record length:
N = (B - 22) / (R + 2)
If your record length is 35 bytes and the block size is 4096, you can compute the
number of records per block as follows:
N = (4096 - 22) / (35 + 2) = 110
Additional Guidelines for Creating Tables
Consider these additional guidelines when creating tables:
•
Specify PARTITION ARRAY EXTENDED to take advantage of the greater number
of partitions and indexes available for tables and indexes on versions 320 and later
of SQL/MP software. Note, however, that DML and DDL statements on tables and
indexes with extended partition arrays can only be performed from nodes running
version 320 or later of SQL/MP software.
•
Specify table attributes that are best for the performance, access, size, and
protection of the data in the base table:
°
Use BLOCKSIZE, EXTENTS, MAXEXTENTS, ALLOCATE, ICOMPRESS,
DCOMPRESS, and RECLENGTH, if applicable, for controlling the size of the
table.