SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-89
Considerations for CREATE TABLE
For small tables, you need not be concerned about the details of this mechanism. For
large tables, however, the indexes used to enforce constraints can require significant
amounts of disk space. You might prefer to create and partition constraint-supporting
indexes directly so that you can control the use of disk space or so that you can specify
indexes that provide more effective access paths for your application than those
created by default to support constraints.
To create constraint-supporting indexes directly, use CREATE TABLE to create the
table without index-implemented constraints, use CREATE INDEX to create
appropriate indexes, and then use ALTER TABLE ... ADD CONSTRAINT to add
constraints to the table.
Limits for Tables
The size of a row of a table is limited to 4040 bytes. The executor reserves 4 bytes for
a header, leaving 4036 bytes for the sum of all the column sizes (including the
SYSKEY column, if the table has a SYSKEY). A SYSKEY has a size of 8 bytes. The
number of bytes required to store a column depends on the data type of that column. If
the column does not have the NOT NULL attribute, NonStop SQL/MX uses an
additional 2 bytes for the NULL indicator for that column. Each variable-length
character column uses an additional 8 bytes for the column length.
Tables and Triggers
If you plan to create triggers on a table, its primary key length cannot exceed 239
bytes. A table which will not have triggers can have a primary key of 255 bytes. For
details about this limit, see Triggers and Primary Keys on page 2-100.
Creating Partitions Automatically
NonStop SQL/MX uses Partition Overlay Specification (POS) so that MXCS and
JDBC/MX users can automatically create hash-partitioned tables with the CREATE
TABLE statement. NonStop SQL/MX does not support automatic creation of range-
partitioned tables.
Applications can control whether POS is enabled, the number of partitions, and the
physical location of the partitions.
Two CONTROL QUERY DEFAULT attributes determine the number and physical
location of the partitions: POS_LOCATIONS and POS_NUM_OF_PARTNS.
POS_RAISE_ERROR controls how errors are displayed. For values and syntax of
these defaults, see Partition Management on page 10-51.
You enable POS by setting the CONTROL QUERY DEFAULT attribute
POS_NUM_OF_PARTNS to a numeric value greater than 1.
To activate POS, these conditions must be true:
•
The POS feature is enabled during execution of a CREATE TABLE statement.