SQL Reference Guide > SQL Reference Guide > SQL Statements > MODIFY > WITH Clause Options for MODIFY
Was this helpful?
WITH Clause Options for MODIFY
The remaining WITH clause options for the MODIFY statement are described below.
ALLOCATION
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.
EXTEND
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.
FILLFACTOR, MINPAGES, and MAXPAGES
WITH 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.
WITH MINPAGES specifies the minimum number of primary pages a hash table must have. WITH 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
LEAFFILL and NONLEAFFILL
For btree tables, the WITH LEAFFILL clause 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 LEAFFILLand 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.
Default: LEAFFILL=70; NONLEAFFILL=80
The MODIFY TO RECONSTRUCT operation does not affect these attributes.
BLOB_EXTEND
To specify the number of pages to be added to a blob extension table, add the WITH BLOB_EXTEND=n clause to MODIFY...TO ADD_EXTEND. For more information, see the description of WITH EXTEND.
LOCATION
To change the location of a table when modifying its storage structure, specify the WITH LOCATION clause. 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.
COMPRESSION
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
Secondary Index
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
 
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.
If a secondary index is compressed (with KEY compression), the non-key columns will also be compressed.
(NO)PERSISTENCE
The WITH [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:
WITH PERSISTENCE
Recreates the index when its base table is modified.
WITH NOPERSISTENCE
Drops the index when its base table is modified.
Default: A MODIFY to a storage structure sets an index to NOPERSISTENCE.
Other MODIFY actions (including MODIFY TO RECONSTRUCT) do not change an index's persistence.
UNIQUE_SCOPE
The WITH UNIQUE_SCOPE option specifies, for tables or indexes with unique storage structures, how uniqueness is checked during an update option.
There are two options:
UNIQUE_SCOPE = ROW
Checks uniqueness as each row is inserted.
UNIQUE_SCOPE = STATEMENT
Checks uniqueness after the UPDATE statement is completed.
Default: UNIQUE_SCOPE = ROW, when first imposing uniqueness on a table.
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.
PAGE_SIZE
Specify page size using WITH 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 8192. The tid size is 8. The buffer cache for the installation must also be configured with the page size you specify in CREATE TABLE or an error occurs.
NOPARTITION | PARTITION=
The WITH PARTITION= clause allows the partitioning scheme of a table to be changed. The table does not have to be partitioned initially. The WITH NOPARTITION clause removes partitioning from a table. For the syntax of a PARTITION= specification, see Partitioning Schemes.
The default for the MODIFY statement is to not change the partitioning scheme of a table.
The with_clause options PARTITION= or NOPARTITION are permitted if the MODIFY statement specifies a storage structure that includes the RECONSTRUCT action.
The WITH PARTITION clause is required when managing partitions with modify actions DROP, MERGE, and SPLIT. For more information, see:
CONCURRENT_UPDATES
The WITH 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.
Syntax to enable a table modification to be performed online:
MODIFY tablename TO HASH ON column WITH CONCURRENT_UPDATES
When the online modify completes, however, exclusive access to the table is still required during a brief period to apply any updates that were made to the table during the online modify. The length of this period depends on the number of updates that must be applied.
Online modification of tables cannot be accomplished in the following:
Actian X clusters
Temporary tables
System catalogs
Partitioned tables
Secondary indexes
Tables with rows that span pages. For more information, see Maximum Row Size Per Page Size in the Database Administrator Guide.
Note:  To use online modification, a database must be journaled.
NODEPENDENCY_CHECK
A table cannot be modified if it destroys indexes needed for constraints. The operation can be forced, however, by using the WITH NODEPENDENCY_CHECK option.
IMPORTANT!  If you use this option, you must preserve or recreate the table structure necessary to enforce the constraints.
PASSPHRASE=
WITH PASSPHRASE specifies an encryption passphrase used to access a table with one or more encrypted columns. The passphrase must match the latest one defined for the table. WITH PASSPHRASE must be used with the ENCRYPT modify action.
The syntax of the WITH PASSPHRASE clause is:
WITH PASSPHRASE='encryption passphrase', [NEW_PASSPHRASE='new encryption passphrase']
NEW_PASSPHRASE lets you change the passphrase. (The passphrase is initially defined on the CREATE TABLE statement.) It must be preceded by the WITH PASSPHRASE clause. The passphrase must be at least eight characters, can contain spaces, and must be enclosed in single quotes.
After the new passphrase is defined using NEW_PASSPHRASE, access to the encrypted table is disabled until an additional MODIFY … ENCRYPT WITH PASSPHRASE statement is issued, which verifies that the new passphrase was typed correctly.
If the second command succeeds you can then issue a COMMIT, confident that you have changed the passphrase correctly. If this series of MODIFY … ENCRYPT … NEW_PASSPHRASE and MODIFY … ENCRYPT commands fails because of a password mismatch, you can retry the second MODIFY (if you mistyped the passphrase) or ROLLBACK the passphrase change attempt to the old passphrase and try again.
Last modified date: 04/26/2024