|
|
|
The remaining with clause options for the Modify statement are described below.
|
|
|
FILLFACTOR specifies the percentage (from 1 to 100) of each primary data page that must be filled with rows, under ideal conditions. For example, if you specify a fillfactor of 40, the DBMS Server fills 40% of each of the primary data pages in the restructured table with rows. You can specify this option with the isam, hash, or btree structures. Take care when specifying large fillfactors because a non-uniform distribution of key values can later result in overflow pages and thus degrade access performance for the table.
MINPAGES specifies the minimum number of primary pages a hash table must have. MAXPAGES specifies the maximum number of primary pages a hash table can have. Minpages and maxpages must be at least 1. If both minpages and maxpages are specified in a modify statement, minpages must not exceed maxpages.
For best performance, the values for minpages and maxpages must be a power of 2. If a number other than a power of 2 is chosen, the DBMS Server can change the number to the nearest power of 2 when the modify executes. To ensure that the specified number is not changed, set both minpages and maxpages to that number.
By default, modify to storage-structure resets these attributes back to their defaults (listed below). The modify to reconstruct operation does not affect these attributes.
Default values for fillfactor, minpages and maxpages are listed in this table:
|
Fillfactor |
Minpages |
Maxpages |
|---|---|---|---|
hash |
50 |
16 |
no limit |
compressed hash |
75 |
1 |
no limit |
Isam |
80 |
n/a |
n/a |
compressed isam |
100 |
n/a |
n/a |
btree |
80 |
n/a |
n/a |
compressed btree |
100 |
n/a |
n/a |
|
|
|
For btree tables, the LEAFFILL parameter specifies how full to fill the leaf index pages. Leaf index pages are the index pages that are directly above the data pages. NONLEAFFILL specifies how full to fill the non-leaf index pages; non-leaf index pages are the pages above the leaf pages. Specify leaffill and nonleaffill as percentages. For example, if you modify a table to btree, specifying nonleaffill=75, each non-leaf index page is 75% full when the modification is complete.
The leaffill and nonleaffill parameters can assist with controlling locking contention in btree index pages. If some open space is retained on these pages, concurrent users can access the btree with less likelihood of contention while their queries descend the index tree. Strike a balance between preserving space in index pages and creating a greater number of index pages. More levels of index pages require more I/O to locate a data row.
By default, modify to storage-structure resets these attributes back to their defaults (listed below). Default values for leaffill and nonleaffill are 70% and 80%, respectively. The modify to reconstruct operation does not affect these attributes.
|
|
|
Use the WITH ALLOCATION option to specify the number of pages initially allocated to the table or index. By pre-allocating disk space to a table, runtime errors that result from running out of disk space can be avoided. If the table is spread across multiple locations, space is allocated across all locations.
The number of pages specified must be between 4 and 8,388,607 (the maximum number of pages in a table). If the specified number of pages cannot be allocated, the modify statement is aborted.
A table can be modified to a smaller size. If the table requires more pages that you specify, the table is extended and no data is lost. A table can be modified to a larger size to reserve disk space for the table.
If not specified, a modify does not change a table's allocation.
|
|
|
To specify the number of pages by which a table or index grows when it requires more space, use the WITH EXTEND clause. The number of pages specified must be between 1 and 8,388,607 (the maximum number of pages in a table). If the specified number of pages cannot be allocated when the table must be extended (for example, during an insert operation), the DBMS Server aborts the statement and issues an error. By default, tables and indexes are extended by groups of 16 pages.
If not specified, a modify does not change a table's extend attribute.
|
|
|
To specify data and key compression, use the WITH COMPRESSION clause. Compression removes the string trim from variable character data. The following table lists valid compression options:
Storage Structure |
Base Table or |
Can Compress Data? |
Can Compress Key? |
|---|---|---|---|
hash |
Base Table |
Yes |
No |
|
Secondary Index |
Yes |
No |
heap |
Base Table |
Yes |
No |
|
Secondary Index |
No |
No |
btree |
Base Table |
Yes |
Yes |
btree |
Secondary Index |
No |
Yes |
Isam |
Base Table |
Yes |
No |
|
Secondary Index |
Yes |
No |
To specify an uncompressed storage structure, specify WITH NOCOMPRESSION.
To compress both key and data for tables where this is valid (primarily btree), specify WITH COMPRESSION, omitting the KEY and DATA clause. To compress data or keys independently of one another, specify WITH COMPRESSION = (KEY|DATA). To compress data using bit compression, specify WITH COMPRESSION = HIDATA. To explicitly suppress compression of data or keys, specify WITH COMPRESSION = (NOKEY | NODATA).
If not specified, modify to storage-structure removes compression, unless the c-prefix variants are used (cbtree and so on). Other variants of modify preserve the table's compression type.
|
|
|
To change the location of a table when modifying its storage structure, specify the LOCATION option. This option specifies one or more new locations for the table. The locations specified must exist when the statement executes and the database must have been extended to those locations. For information about areas and extending databases, see the Database Administrator Guide.
|
|
|
The UNIQUE_SCOPE option specifies, for tables or indexes with unique storage structures, how uniqueness is checked during an update option.
There are two options:
The default when first imposing uniqueness on a table is unique_scope = row. Specify the unique_scope option only when modifying to a unique storage structure. For example:
modify mytable to btree unique with unique_scope = row;
If not otherwise specified, a modify does not change the unique_scope setting.
|
|
|
The [NO]PERSISTENCE option specifies whether an index is recreated when its related base table is modified. This option is valid only for indexes. There are two options:
By default, a modify to storage structure sets an index to nopersistence. Other modify actions (including modify to reconstruct) do not change an index's persistence.
|
|
|
Specify page size using PAGE_SIZE = n where n can be the page size in the following table:
Page Size |
Number of Bytes |
|---|---|
2K |
2,048 |
4K |
4,096 |
8K |
8,192 |
16K |
16,384 |
32K |
32,768 |
64K |
65,536 |
The default page size is 2,048. The tid size is 4. The buffer cache for the installation must also be configured with the page size you specify in create table or an error occurs.
The page size of a session temporary table cannot be changed by a modify.
|
|
|
The PARTITION= clause allows a table's partitioning scheme to be changed. The table need not be partitioned initially. The NOPARTITION clause removes partitioning from a table. For the syntax of a partition= specification, see Partitioning Schemes.
When the partition= or nopartition with-clause options are used in a modify statement, they are permitted only if the modify statement specifies a storage structure (this includes the reconstruct action). Other forms of the modify statement (for example, modify to truncated) do not allow either partition= or nopartition clauses.
The default for the modify statement is to not change the table's partitioning scheme.
|
|
|
The CONCURRENT_UPDATES option specifies that a table modify is to be performed online. Unlike a regular modify, which locks out all other access to the table for the entire duration, an online modify permits normal read and update access to the table for most of the modify. There is a brief period at the end of the modify operation where exclusive access to the table is still required.
Online modification of tables cannot be accomplished in the following:
Note: To use online modification, a database must be journaled.
|
|
|
A table cannot be modified if it destroys indexes needed for constraints. The operation can be forced, however, by using the NODEPENDENCY_CHECK option.
Important! If you use this option, you must preserve or recreate the table structure necessary to enforce the constraints.