SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-23
Considerations for STORE BY
Considerations for STORE BY
Storage Order and Partitioning
The organization of the physical files that make up a table and the order of rows within
those physical files determine the ways you can partition the table and affect the
performance of queries on that table.
You specify the organization and storage order with the STORE BY clause of the
CREATE TABLE statement (either explicitly or by omitting the clause), and you cannot
change it after the table is created. There are three possibilities.
Primary Key Storage Order
If you specify STORE BY PRIMARY KEY or you omit the STORE BY clause but
specify a PRIMARY KEY clause that has the NOT DROPPABLE option, NonStop
SQL/MX stores and retrieves rows in the order of the values in the primary key and
allows you to partition the table based on values of the primary key.
This ordering mechanism is generally the most efficient method if you want to partition
by values of a unique key.
SYSKEY Storage Order
If you omit the STORE BY clause and do not specify a PRIMARY KEY that has the
NOT DROPPABLE option, NonStop SQL/MX determines the storage order for rows
without reference to the data you specify for the rows.
As a mechanism for determining row order, NonStop SQL/MX creates the table with an
additional column named SYSKEY (type LARGEINT SIGNED) and automatically
generates a unique eight-byte number as the SYSKEY value of each row you insert in
the table. Rows are stored and retrieved in ascending order by the SYSKEY value. You
cannot update values in the SYSKEY column, although you can list them if you
explicitly name SYSKEY in a SELECT statement. (SELECT * does not include
SYSKEY.) See SYSKEYs on page 6-61.
You cannot partition a table stored only by the SYSKEY.
Key Column List Storage Order
If you specify STORE BY key-column-list and do not have a NOT DROPPABLE
PRIMARY KEY, NonStop SQL/MX orders the table using a combination of the two
methods previously described and allows you to partition based on values of the
columns in key-column-list.
NonStop SQL/MX creates a SYSKEY column and treats it as the last column in a key
that begins with the column or columns you specified in key-column-list. The
SYSKEY column makes the overall key unique, even though the columns you
specified might not be unique. NonStop SQL/MX then stores and retrieves rows in the
order of the values in the overall key (the columns in key-column-list followed by
the SYSKEY column) just as if it were a primary key.