Neoview SQL Reference Manual (R2.5)

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, ATTRIBUTE, and DISK POOL clauses. CREATE TABLE LIKE is also an
extension.
Considerations for DISK POOL
The DISK POOL attribute allows disks to be divided into disk pools. The following rules apply
to DISK POOL:
• For a Neoview system with 2 disks per CPU, valid number of pools are 1, 2, 4, 8, 16, 32
• For a Neoview system with 3 disks per CPU, valid number of pools are 1, 3, 6, 12, 24, 48
• For a Neoview system with 4 disks per CPU, valid number of pools are 1, 2, 4, 8, 16, 32, 64
The maximum number of pools allowed is the number of disks per segment. In this regard, a
Neoview system with:
• 2 disks per CPU — the maximum number of pools allowed is 32 since there are 32 disks per
segment (16 CPUs * 2 disks per CPU)
• 3 disks per CPU — the maximum number of pools allowed is 48 since there are 48 disks per
segment (16 CPUs * 3 disks per CPU)
• 4 disks per CPU — the maximum number of pools allowed is 64 since there are 64 disks per
segment (16 CPUs * 4 disks per CPU)
• The default number of pools for systems up to 256 disks is 1.For systems beyond 256 disks,
the default number of pools within a system is based on this formula:
— the number of disks in the system divided by 256, rounded to the nearest number of
valid pools
For example, in a system with 480 total disks (2 disks per CPU), the default number of pools
is 2 (480/256=1.87, rounded to the next valid disk pool, which is 2). In a system with 480 total
disks (3 disks per CPU), the default number of pools is 3 (480/256=1.87, rounded to the next
valid disk pool, which is 3).
• 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.
— For pools with 3 disks per CPU, the first disk of every CPU belongs to pool 1, the second
disk of every CPU belongs to pool 2, and the third disk of every CPU belongs to pool
3.
— For 2 pools with 4 disks per CPU, the first disk and third disk of every CPU belongs to
pool 1 and the second disk and fourth disk of every CPU belongs to pool 2.
• Tables can be allocated to disks that belong to the requested disk pool number.
• By default, tables are assigned to disk pools in a round robin fashion.
• The default number of partitions created are as many number of disks in a disk pool.
• A non partitioned table can be created within a disk pool using the NO PARTITION clause.
Restrictions for DISK POOL
DISK POOL cannot be used with volatile tables, materialized views, indexes, and CREATE
TABLE LIKE.
92 SQL Statements