SQL/MX Release 2.0 Best Practices

Database Sizing Considerations 22
When designing a database distributed across multiple systems, you have several alternatives for locating
tables. You must consider the location of the fact, dimension, and summary tables, as well as secondary
indexes.
You might choose to keep each table entirely on one system, but distribute the collection of tables over
all systems, which is feasible if the division of tables coincides with the workload division. This approach is
the easiest to manage and makes efficient use of resources, including disk space.
Alternatively, you might choose to distribute the fact tables across systems and replicate all dimension
tables, summary tables, and secondary indexes, which is feasible if the division of fact tables coincides
with the division of their use; users on a specific system access data only on that system. This approach
provides good performance, but requires replicating large amounts of data.
Most customers cannot effectively maintain data locality for query access because of the ad-hoc nature
of query processing. Consequently, customers usually distribute the fact tables across systems, and may
or may not replicate dimension tables, summary tables, and secondary indexes. Many customers avoid
dealing with this issue by migrating to the latest hardware technology that allows them to use a single
system.
Additional Information
Renaming a Table
NonStop SQL/MX does not provide a mechanism for renaming tables. However, you can use these
workaround:
1. Create temporary or additional tables.
2. Load the data to the new table or tables.
3. Recreate the original table.
4. Load the data back to the recreated table using the original table name.
Limits
This list describes limits for various parts of NonStop SQL/MX Release 2.0:
• Constraints
The maximum combined length of the columns for a REFERENCE, PRIMARY KEY, or UNIQUE constraint
is 255 bytes.
• DROP SCHEMA CASCADE transaction limits
You might need to increase the number of locks for each volume; otherwise, DROP SCHEMA
CASCADE might fail.
• EXTENTS
Limited only by the size of the disk.
• FROM clause of the SELECT statement
16 SQL/MP tables are allowed. For SQL/MX Release 2.0 tables, NonStop SQL/MX generates good
plans up to approximately 40 tables. Beyond that number, executor performance is adversely
affected.
• IN predicate
1900 expressions are allowed.
• Indexes
The maximum combined length of the columns for an index is 255 bytes. A nonunique index consists
of columns and a clustering key. A unique index consists only of columns.
No restriction exists on the number of indexes for each table; however, creating many indexes on a
table affects performance.