Neoview SQL Reference Manual (R2.4)

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 same partitioning column or columns as the base table.
Partitioning columns cannot be floating-point data columns.
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.
If you do not specify MAX TABLE SIZE, a table is created with the following default
characteristics:
• For a partitioned table, the space allocated after you enter the first row is 25 MB times
number of partitions. The maximum size a partitioned table can reach is the size of the
disk at the time of table creation. When a partitioned table is created, the table is
automatically partitioned across all the disk volumes on the system.
• For a non-partitioned table, the space allocated after you enter the first row is 25 MB.
DISK POOL pool_number
causes the table to be created in the specified pool number. The value of pool_number is
an unsigned integer. Zero (0) is not a valid pool number. If pool_number is greater than
CREATE TABLE Statement 73