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-5

Partitioning Key

You specify a partitioning key through the PARTITION BY clause of the CREATE

TABLE or CREATE INDEX statement. The FIRST KEY option of the PARTITION

clause specifies the beginning of the range for a range-partitioned table or index

partition. The FIRST KEY clause specifies the lowest values in the partition for

columns stored in ascending order and the highest values for columns stored in

descending order. Hash partitions have a partitioning key that translates into a hash

value. All rows that translate into the same value are assigned to the same hash

partition. All these column values are referred to as the partitioning key.

For more information about the partitioning key, see Creating and Using a Partitioning

Key on page 7-14, Creating Indexes for SQL/MX Tables on page 7-31, and the

SQL/MX Reference Manual.

Foreign Key

The foreign key is the column or set of columns specified in the FOREIGN KEY clause

of ALTER TABLE or CREATE TABLE, immediately following the FOREIGN KEY

keywords. The foreign key columns can contain only values that match those in the

column or set of columns specified in the REFERENCE clause of ALTER TABLE or

CREATE TABLE.

The two foreign key columns must have the same characteristics (data type, length,

scale, precision), and there must be a UNIQUE or PRIMARY KEY constraint on the

column or set of columns specified in the REFERENCE clause.

The Key-Sequenced File Structure

All SQL/MX tables are key-sequenced files, which store rows (records) that contain a

clustering key. New rows are stored in sequence by clustering key value. A user

performing update operations can update or delete rows and lengthen or shorten

values in a varying-length column (VARCHAR, NCHAR VARYING) when the column is

not part of the clustering key.

Rows are stored in a key-sequenced file logically in ascending or descending order,

according to their clustering key values, as defined by the use of the ASCENDING or

DESCENDING specification in the CREATE TABLE statement.

For information about the file structures of SQL/MP tables, see the SQL/MP Installation

and Management Guide and the SQL/MP Reference Manual.

Types of Key-Sequenced File Access

You can access key-sequenced files either sequentially or randomly. Sequential

access is preferable, for example, when generating a report of the quantity on hand of

all parts in an inventory file. Random access is preferable when you want to identify the

vendor of a particular part. The SQL/MX optimizer determines whether sequential or

random access is performed.