With_Clause for Create Table...As Select
Create table...as select accepts the following options on the with clause, specified as a comma-separated list:
allocation = n
Specifies the number of pages initially allocated for the table.
Limits: Integer between 4 and 8,388,607.
Default: 4
extend = n
Specifies the number of pages by which the table is extended when more space is required.
Limits: Integer between 1 and 8,388,607
Default: 16
structure = structure
Specifies the storage structure of the new table. Valid values include: BTREE, ISAM, HEAP, or HASH.
key = (column_name {, column_name})
Specifies the columns on which your table is keyed. All columns in this list must also be specified in the subselect. Be advised that this option affects the way data is physically clustered on disk.
fillfactor = n
Specifies the percentage of each primary data page that must be filled with rows (under ideal conditions). Fillfactor is not valid if a heap table is being created.
Note: Large fillfactors in combination with a non-uniform distribution of key values can cause a table to contain overflow pages, increasing the time required to access the table.
Limits: 1 to 100
minpages = n
Specifies the minimum number of primary pages a hash table must have when created.
Limits: Minimum value is 1. Cannot exceed the value of maxpages, if specified.
maxpages = n
Specifies the maximum number of primary pages a hash table can have when created.
Limits: Minimum value is 1.
compression [= ([[no]key] [,[no]data])] | nocompression
Specifies whether the key or data is to be compressed. If compression is specified, the structure clause must be specified.
leaffill = n
(B-tree tables only) Specifies the maximum percentage full for leaf index pages. Leaf pages are the index pages directly above the data pages.
Default: 70
nonleaffill = n
(B-tree tables only) Specifies the maximum percentage full for nonleaf index pages.
Default: 80
priority = n
Specifies cache priority. If an explicit priority is not set for an index belonging to a base table to which an explicit priority has been assigned, the index inherits the priority of the base table.
Limits: Integer between 0 and 8, with 0 being the lowest priority and 8 being the highest.
Default: 0. (Causes the table to revert to a normal cache management algorithm.)