Neoview SQL Reference Manual (R2.2)

name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL,
primary key (surrogate_key,order_number)
)
HASH PARTITION BY(surrogate_key, order_number);
create unique index sk_idx on t_id(surrogate_key);
• This example shows the IDENTITY column surrogate_key as the partitioning key. Note
that for this release, the surrogate key column must have a unique index on it:
NOTE: In Neoview SQL, the partitioning key must be a subset of the clustering key. In the
case of a table with a single column clustering key, the partitioning key must be the same
as the clustering key.
CREATE TABLE t_id (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL,
primary key (surrogate_key,order_number)
)
HASH PARTITION BY(surrogate_key);
create unique index sk_idx on t_id(surrogate_key);
• This statement fails with an error stating that only LARGEINT columns can be defined as
an IDENTITY column.
CREATE TABLE T (surrogate_key CHAR(64) GENERATED BY
DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL)
HASH PARTITION BY(surrogate_key);
• This statement fails with an error stating that a table can have only one IDENTITY column.
CREATE TABLE T (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
name CHAR (256) NOT NULL,
order_number LARGEINT GENERATED BY DEFAULT AS
IDENTITY NOT NULL)
HASH PARTITION BY(surrogate_key);
Neoview SQL Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. Neoview SQL
extensions to the CREATE TABLE statement are ASCENDING, DESCENDING, PARTITION,
MAXTABLESIZE, and ATTRIBUTE clauses.
DISK POOL
The DISK POOL attribute allows disks to be divided into disk pools.
Considerations for DISK POOL
• The default number of pools for systems up to 256 disks is 1 and for systems beyond 256
disks, the default number is 2.
• Each disk pool has the same number of disks.
• The number of disks in a pool is the total disks divided by the number of pools.
• Disks within a pool are evenly distributed among segments.
• Distribution of disks to disk pools — For 2 pools with 2 disks per CPU, the first disk of every
CPU belongs to pool 1 and the second disk of every CPU belongs to pool 2.
• Tables can be allocated to disks that belong to the requested disk pool number.
CREATE TABLE Statement 77