With_Clause for Create Table...as Select

The CREATE TABLE...AS SELECT statement accepts the following options on the WITH clause:

• 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

  Specifies the maximum percentage full for leaf index pages (B-tree tables only). Leaf pages are the index pages directly above the data pages.

  Default: 70

• NONLEAFFILL = n

  Specifies the maximum percentage full for nonleaf index pages (B-tree tables only).

  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.)