SQL/MX Release 2.0 Best Practices

Database Sizing Considerations 23
No restrictions exist on the number of partitions that an index supports; however, beyond 512
partitions, performance and memory issues can occur.
• INSERT operations
150 records can be inserted into SQL/MX tables in a single INSERT operation.
250 records can be inserted into SQL/MP tables in a single INSERT operation.
• Joins
40 tables can be joined, including base tables of views, but joining more tables affects
performance.
• MAXEXTENTS size
NonStop SQL/MX supports 768 (compared with 919 for NonStop SQL/MP).
• Partitions
No restrictions exist on the number of partitions that a table can support; however, beyond 512
partitions, performance and memory issues can occur.
Partitions can be on the same physical disk as the main file.
• PFS (Process File Segment) usage is decreased in the file system
This issue affects the number of opens.
• Referential constraints
A table can have an unlimited number of referential constraints, and you can specify the same
foreign key in more than one referential constraint, but you must define each referential constraint
separately.
• Tables
The maximum length of a row is 4040 bytes, but not all of that space is available. Depending on the
data types that you use, NonStop SQL/MX will always use some bytes for internal use.
The clustering key length for a table cannot be longer than 255 bytes. If the table has triggers, the
clustering key length cannot be longer than 247 bytes; if the key length is greater than 247, trigger
creation will fail.
Practical Limitations
If you do not need parallel execution, or you can use an outer table with fewer partitions and join to the
larger table, it can be futile to create such a large table, even though it is possible. In this case, you must
reduce the number of partitions by omitting either columns or rows from the table.
Techniques for Creating Large Tables
Using a Single CREATE TABLE Statement
Writing a CREATE TABLE DDL statement for a large table in MXCI is tedious because of the large number
of partitions involved. Instead, you should use an OSS script or other means to generate the DDL
statement. Tools such as Perl can be used to automate this process.
Additional Considerations Tables
The LOCATION clause in the CREATE TABLE statement provides the ability to assign a physical location for
a table and its partitions. It also provides the capability to assign a more meaningful name for each
partition. A good practice is to use the LOCATION clause to assign physical locations and meaningful
names. Otherwise, NonStop SQL/MX could generate a random name for each partition.
The LOCATION clause is also used in the MODIFY utility. If you move a partition, the initial name used for
the partition in the LOCATION clause of the CREATE TABLE is not maintained. If the LOCATION clause is not
used when a partition is moved, a new SQL/MX-generated name will be created. In order to maintain a
consistent naming conversion, you must use the LOCATION clause.