NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-9
Limits
The formula for applying the limit to a partition is:
PRIM + ( SEC * (MAXEXT -1) ) <= 2.1465 gigabytes
PRIM Primary extent size
SEC Secondary extent size
MAXEXT Maximum number of extents
A partition must fit on a single disk, however, so the hardware limits of a particular
disk might also limit the size of a partition on that disk.
•
Partitions per index or table, extended partition array
Typically, if the primary key of the index or table is between 10 and 50 bytes, the
maximum number of partitions allowed ranges from 900 (for the smaller key size)
down to 400 (for the larger key size).
The maximum number of index partitions depends on the key size; if the primary
key of a key-sequenced table is between 10 and 50 bytes and the index key size is
also between 10 and 50 bytes, the maximum number of partitions typically ranges
from 674 (for keys of 10 bytes) to 246 (for keys of 50 bytes).
The actual calculation of the limit is complex and depends on such factors as disk
label space and the message size of the NonStop Kernel.
When you create or alter a table or index with a large number of partitions, the
PARTNS catalog table and associated IXPART01 index might become full. To
correct the situation, distribute object and partition definitions across multiple
catalogs.
Actual limits depend upon the definition of the SQL tables and indexes, but the
PARTNS and IXPART01 catalog tables can contain approximately 500,000 rows.
Each table or index with N partitions stores N**2 rows of information in the
PARTNS catalog table. Thus, three tables of 400 partitions each can be defined in a
single catalog.
DDL and DML operations on tables or indexes that have large numbers of partitions
might return file-system error 31 or 34 due to insufficient memory in the Process
File Segment (PFS) used by the SQL file system. Actual limits depend upon the
definition of the SQL tables and indexes as well as the SQL statement being
executed, but memory limitations typically appear when a table or index has 400 or
more partitions.