WITH Clause for CREATE TABLE...AS SELECT
CREATE TABLE...AS SELECT accepts the following additional options on the WITH clause, specified as a comma-separated list:
STRUCTURE = structure
Specifies the storage structure of the new table. Valid values include: BTREE, ISAM, HEAP, HASH, X100, and X100_ROW.
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.
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
Last modified date: 01/30/2023