Neoview SQL Reference Manual (R2.5)

upon insert. Duplicate row means an entire row inclusive of all columns. If the row being
inserted has only identical key columns, a duplicate key error is generated, much like normal
table behavior.
VOLATILE
specifies a VOLATILE table, which is a table limited to the session that creates the table. After
the session ends, the table is automatically dropped.
NO PARTITION
creates a non-partitioned table. To create a non-partitioned table, specify the NO PARTITION
option in the CREATE TABLE statement.
If the NO PARTITION option is not specified, a partitioned table is created. When a partitioned
table is created, the table is automatically partitioned across all the disk volumes within a
disk pool.
HASH PARTITION BY (partitioning-column, partitioning-column...)]
specifies the partitioning columns. If you do not specify the partitioning columns, the default
is the primary key column list.
Partitioning columns cannot be floating-point data columns, NULL columns, and you cannot
specify CASESPECIFIC or NOT CASESPECIFIC.
STORE BY { PRIMARY KEY | (key-column-list)}
specifies a set of columns on which to base the clustering key. The clustering key determines
the order of rows within the physical file that holds the table. The storage order has an effect
on how you can partition the object.
PRIMARY KEY
bases the clustering key on the primary key columns.
key-column-list
bases the clustering key on the columns in the key-column-list. The key columns in
key-column-list must be specified as NOT NULL. If STORE BY is not specified, then
the clustering key is the PRIMARY KEY.
MAX TABLE SIZE megabytes
specifies the initial table size, which is the space allocated for the table after the first row is
inserted as a number of megabytes.
The value set by this option is mainly used by Neoview SQL to calculate the primary extent
size and the secondary extent size and maximum extents of the table, based on internal rules.
However, the actual maximum size of the table as shown by HPDM is much larger than the
value set by this option. The value shown by HPDM is the size that table can grow to if there
is a need.
The primary extent size is the space allocated for every partition after the first insert into the
table and secondary extent size is the space allocated after the primary extent space is used
up. Multiple secondary extents can be calculated by Neoview SQL as maximum extents.
When one secondary extent space is used up, then another secondary extent is allocated on
the next insert, and so on. In most cases, except for very small tables, the maximum extents
are chosen in such a way that the partitions can grow to the size of the disk.
Neoview SQL calculates the primary extent size, secondary extent size, and maximum extents
of a partition in this manner:
Max Partition Size = value set by this option / number of partitions
Primary Extent Size = minimum of (Max Partition Size, 1000 MB)
Secondary Extent Size = maximum of (Primary Extent Size * 0.25,
minimum of (Max Partition Size - Primary Extent Size, 1000 MB))
74 SQL Statements