MODIFY Examples
The following examples provide details.
Example 1:
Modify the "employee" table to an indexed sequential storage structure with eno as the keyed column:
modify employee to isam on eno
If "eno" is the first column of the "employee" table, the same result can be achieved by
modify employee to isam
Example 2:
Perform the same modify, but request a 60% occupancy on all primary pages:
modify employee to isam on eno
with fillfactor = 60
Example 3:
Modify the "job" table to compressed hash storage structure with "jid" and "salary" as keyed columns:
modify job to hash on jid, salary
with compression
Example 4:
Perform the same modify, but also request 75% occupancy on all primary pages, a minimum of 7 primary pages and a maximum of 43 primary pages:
modify job to hash on jid, salary
with compression, fillfactor = 75,
minpages = 7, maxpages = 43
Example 5:
Perform the same modify again but only request a minimum of 16 primary pages:
modify job to hash on jid, salary
with compression, minpages = 16
Example 6:
Modify the "dept" table to a heap storage structure and move it to a new location:
modify dept to heap with location=(area4)
Example 7:
Modify the "dept" table to a heap again, but have rows sorted on the "dno" column and have any duplicate rows removed:
modify dept to heapsort on dno
Example 8:
Modify the "employee" table in ascending order by "ename," descending order by "age," and have any duplicate rows removed:
modify employee to heapsort on ename asc,
age desc
Example 9:
Modify the "employee" table to btree on "ename," so that data pages are 50% full and index pages are initially 40% full:
modify employee to btree on ename
with fillfactor = 50, leaffill = 40
Example 10:
Modify a table to btree with data compression, no key compression. This is the format used by the (obsolete) cbtree storage structure:
modify table1 to btree
with compression=(nokey, data)
Example 11:
Modify an index to btree using key compression:
modify index1 to btree with compression=(key)
Example 12:
Modify an index so it is retained when its base table is modified:
modify empidx to btree with persistence
Example 13:
Modify a table, specifying the number of pages to be initially allocated to it and the number of pages by which it is extended when it requires more space:
modify inventory to btree
with allocation = 10000, extend = 1000
Example 14:
Modify an index to have uniqueness checked after the completion of an update statement:
modify empidx to btree unique on empid
with unique_scope = statement
Last modified date: 08/14/2024