SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-34
Creating Table Partitions
The trade-off of using PHYSVOL is that when you assign a partition to a particular
physical volume, SMF might not be able to relocate the file (partition) automatically. For
the syntax and definition of the PHYSVOL option, see the CREATE and ALTER entries
in the SQL/MP Reference Manual.
Defining a Large Number of Partitions
To take advantage of the larger number of indexes and the larger number of partitions
available for tables and indexes on versions 320 and later of SQL software, use the
PARTITION ARRAY EXTENDED option when you create the base table.
The length of the key is a factor in determining the maximum number of partitions for a
table or index and relates to the storage of FIRST KEY values for all the partitions in a
fixed-size file label; however, the actual calculation is complex and depends on
additional factors, such as disk label space and the message size of the operating
system. Examples of partition limits for base tables are:
Examples of partition limits for indexes are:
Note that the size of the primary key of an index table equals (primary key of the base
table + index key + 2 bytes). The maximum number of partitions for a base table with a
primary key length of 44 bytes is the same as that of an index table with a primary key
of 10 bytes, an index key of 32 bytes, and the 2-byte offset.
Primary Key
Size (bytes)
Approximate Limit on Number of Partitions
(with EXTENDED partition array)
10 900
44 450
100 250
250 110
Primary Key
Size (bytes)
Index Key Size
(bytes)
Approximate Limit on Number of
Partitions (with EXTENDED partition
array)
10 10 670
10 32 450
50 48 250
100 100 130