Neoview SQL Reference Manual (R2.4 SP2)

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.
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))
Maximum Extents = minimum of ((Disk Size - Primary Extent Size) /
Secondary Extent Size, 768);
In most cases, the space allocated for the table after the first row is inserted is the value set
by this option, unless the value set is too big. If the value set by this option is less than or
equal to 1024 MB times the number of partitions, then the space allocated after the first row
is inserted is the value set by this option. In other cases, where the value set by this option is
greater than 1024 MB times the number of partitions, then the initial table space allocated
will be 1024 MB times the number of partitions. The rest of the space will be allocated as
needed when the secondary extents are allocated.
74 SQL Statements