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

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-8
Planning Table and Index Partitioning
block if not at the end of the file, and gives the index block a pointer to the new data
block.
Key-sequenced files are also used to store indexes. When an index block fills up, it is
split in a similar manner: a new index block is allocated, and some of the pointers are
moved from the old index block to the new one. The first time a split occurs in a file, the
disk process must generate a new level of indexes. The disk process does this by
allocating a higher-level index block containing the low key and a pointer to the two or
more lower-level index blocks, which in turn point to many data blocks. The disk
process must do this again each time the highest-level block is split.
The disk process can perform a three-way block split, when appropriate, creating two
new blocks and distributing the original block’s data and pointers plus the new record
or pointer among all three.
In a key-sequenced file, data blocks are chained together with forward and backward
pointers stored in the header of each block. Data records within each block are stored
in key-value sequence. This storage arrangement enhances the sequential
performance for tables stored in key-sequenced files. Index blocks, however, are not
chained.
Planning Table and Index Partitioning
With medium to very large databases, it is often necessary to use partitioned tables
and indexes to break down the data into smaller and more manageable units. Each
partition can be managed individually and can function independently of the other
partitions.
Partitioning can provide significant benefits to a wide variety of applications by
improving manageability, performance, and availability. It is not unusual for partitioning
to improve the performance of certain queries or maintenance operations by an order
of magnitude.
Range Partitioning and Hash Partitioning
NonStop SQL/MX supports range partitioning and hash partitioning.
Range partitioning uses ranges of column values to map rows to partitions. Range
partitions are ordered by defining the lower and upper boundary for a specific partition.
Rows from all tables or indexes that contain key values that fall within a certain range
are placed on a particular disk. For example, rows for employee numbers 1-500 might
be placed on disk 1, rows for employee numbers 501-1000 placed on disk 2, and so
on. This approach offers good performance for range-based queries and reasonable
performance for exact-match queries involving the partitioning key. You can also put
multiple partitions on the same disk.
Hash partitioning uses a hash function on the partitioning columns to stripe data into
partitions. The hash function is applied to the value of a table or index’s clustering or
partitioning key. The output of this function causes the data for that row to be targeted