Supplement to the HP Neoview SQL Reference Manual (R2.3 SP2)

1 Volatile Table Enhancements
For Neoview Release 2.3 Service Pack 2, Neoview SQL implements these enhancements for
volatile tables:
• Allows users to explicitly specify primary key, STORE BY, and HASH PARTITION BY
clauses on columns that contain null values.
• Does not require that the first column in a volatile table contains not null values and be the
primary key. Instead, Neoview SQL attempts to partition the table, if possible, using an
appropriate suitable key column as the primary and partitioning key. For more information,
see “How Neoview SQL Selects Suitable Keys” (page 9).
How to Enable the Volatile Table Enhancements
For this Neoview release, the volatile table enhancements documented in this chapter must be
enabled by setting the default attribute VOLATILE_TABLE_FIND_SUITABLE_KEY to the
attribute value ON. The Neoview default value is OFF. Contact HP support to set this system
defaults to ON.
The volatile table enhancements can only enabled for volatile tables and do not apply to volatile
indexes.
How Neoview SQL Supports Nullable Keys
• Allows nullable keys in primary key, HASH PARTITION BY, STORE BY, and unique
constraints.
• A null value is treated as the highest value for that column.
• A null value as equal to other null values and only one value is allowed for that column.
How Neoview SQL Selects Suitable Keys
Neoview SQL searches for the first suitable column in the list of columns of the table being
created. Once the column is located, the table is partitioned on it. The searched columns in the
table might be explicitly specified (as in a CREATE TABLE statement) or implicitly created (as
in a CREATE TABLE AS SELECT statement).
The suitable key column is selected only if no primary key, HASH PARTITION BY clause, or
STORE BY clause has been specified in the statement. If any of these clauses have been specified,
they are used to select the key columns.
Neoview SQL follows these guidelines to search for and select suitable keys:
• A suitable column can be a nullable column.
• Certain data types in Neoview SQL cannot be used as a partitioning key. Currently, this
includes any floating point columns (REAL, DOUBLE PRECISION, and FLOAT).
• Neoview SQL searches for a suitable column according to this predefined order:
— Numeric columns are chosen first, followed by fixed char, datetime, interval, and varchar
data types.
— Within numeric data types, the order is binary numeric (largeint, integer, smallint),
decimal, and bignum.
— An unsigned column is given preference over a signed column.
— A non-nullable column is given preference over a nullable column.
— If all data types are the same, the first column is selected.
How to Enable the Volatile Table Enhancements 9