SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-19
Creating Logical Views of Data
•
Data integrity checking by constraints
°
Constraints can greatly enhance the flexibility of programs so that applications
move easily from one set of users to another.
°
Constraints simplify the change process to a simple, online process. If you add
one constraint, the system immediately applies the constraint to all subsequent
transactions. The constraint creation process also checks the existing table to
ensure that all existing rows conform to the new constraint.
°
When NonStop SQL/MX verifies the constraints on the input data, the potential
message traffic between servers and requesters might be increased when
error messages are generated on invalid data.
Creating Logical Views of Data
Logical views of the database are groupings of data different from the physical
database. NonStop SQL/MX is efficient at presenting data in logical views, joining
tables or other views to create a new window into the data. These logical views can
specify only those columns or rows of data that meet given criteria. NonStop SQL/MX
returns only the subset of data, if any, that meets the criteria, thereby reducing
message data transfer between the DP2 process and your program.
You can predefine and name logical views with the CREATE VIEW statement, or you
can create views logically with a SELECT statement. The performance of these two
methods to obtain the same data is equivalent.
For more information, see the information on creating views of tables in Section 7,
Creating an SQL/MX Database.
Adding and Dropping Partitions
For performance improvement, consider partitioning a table or index so that the table
or index spans multiple volumes or multiple nodes. As the number of rows in the table
or index increases, consider redistributing rows across partitions to balance the
distribution of rows. For range-partitioned tables and indexes, you can use the
MODIFY TABLE and MODIFY INDEX statements to split partitions, move partitions,
and move partition boundaries. For hash-partitioned tables and indexes, you can use
MODIFY to add, drop, or move partitions. For range-partitioned tables, you can use the
REUSE option.
When all information in a partition becomes obsolete, or when a database design
deficiency leaves a partition continually empty, a reference to a table or index defined
across this partition results in an unnecessary message to the partition. This message,
in turn, results in a correspondingly longer access time to the table or index. In such
circumstances, you might want to drop this partition while leaving the others defined for
the object intact.