Neoview SQL Reference Manual (R2.4 SP2)

How Neoview SQL Selects Suitable Keys for Volatile Tables
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.
• If a suitable column is not located, the volatile table becomes a non-partitioned table with
a system defined SYSKEY as its primary key.
• If a suitable column is located, it becomes the partitioning key where the primary key is
suitable_column, SYSKEY. This causes the table to be partitioned while preventing the
duplicate key and null-to-non-null errors.
Table 2-1 shows the order of precedence, from low to high, of data types when Neoview SQL
searches for a suitable key. A data type appearing later has precedence over previously-appearing
data types. Data types that do not appear in Table 2-1 cannot be chosen as a key column.
Table 2-1 Precedence of Data Types During Suitable Key Searches
Precedence of Data Types (From Low to High)
VARCHAR
INTERVAL
DATETIME
CHAR(ACTER)
DECIMAL (signed, unsigned)
SMALLINT (signed, unsigned)
INTEGER (signed, unsigned)
LARGEINT (signed only)
Creating Nullable Constraints In a Volatile Table
These examples show the creation of nullable constraints (primary key, HASH PARTITION BY,
STORE BY, and unique) in a volatile table:
create volatile table t (a int, primary key(a));
create volatile table t (a int, primary key(a));
create volatile table t (a int) store by (a);
create volatile table t (a int, primary key(a)) partition by (a);
80 SQL Statements