MODIFY Examples
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;
2. Redo the isam structure on the employee table, but request a 60% occupancy on all primary pages.
MODIFY employee TO RECONSTRUCT
WITH FILLFACTOR = 60;
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;
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;
5. Perform the same modify again but request a minimum of 16 primary pages.
MODIFY job TO HASH ON jid, salary
WITH COMPRESSION, MINPAGES = 16;
6. Modify the dept table to a heap storage structure and move it to a new location.
MODIFY dept TO HEAP WITH LOCATION=(area4);
7. Modify the dept table to a heap again, but sort rows on the dno column and remove any duplicate rows.
MODIFY dept TO HEAPSORT ON dno;
8. Modify the employee table in ascending order by ename, descending order by age, and remove any duplicate rows.
MODIFY employee TO HEAPSORT ON ename ASC,
age DESC;
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;
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);
11. Modify an index to btree using key compression.
MODIFY index1 TO BTREE WITH COMPRESSION=(KEY);
12. Modify an index so it is retained when its base table is modified. Its current table structure is unchanged.
MODIFY empidx TO RECONSTRUCT WITH PERSISTENCE;
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;
14. Modify an index to have uniqueness checked after an UPDATE statement completes.
MODIFY empidx TO BTREE UNIQUE ON empid
WITH UNIQUE_SCOPE = STATEMENT;
15. Move all physical partitions of the table in the CREATE TABLE Example #17 (see
CREATE TABLE Examples) that contain 2001 and earlier ship-dates to the history_loc location.
MODIFY lineitems PARTITION p1 TO REORGANIZE
WITH LOCATION = (history_loc);
16. Remove partitioning from a table.
MODIFY lineitems TO RECONSTRUCT WITH NOPARTITION;
17. Modify the table to disable encryption and decryption (which prevents all table access).
MODIFY secrets ENCRYPT WITH PASSPHRASE='';
18. Modify the encrypted table to re-enable access.
MODIFY secrets ENCRYPT WITH PASSPHRASE='to encrypt or not encrypt, that is the question';
19. Modify the encrypted table's passphrase, and immediate verify and commit the new passphrase.
MODIFY secrets ENCRYPT WITH PASSPHRASE='to encrypt or not encrypt, that is the question', NEW_PASSPHRASE='now is the passphrase of our discontent changed';
MODIFY secrets ENCRYPT WITH PASSPHRASE='now is the passphrase of our discontent changed';
COMMIT;
20. Split partition aa1 of table into two partitions, aa1a and aa1b, where partition aa1a contains rows in column aa that are greater than or equal to 25, and aa1b contains values in column aa that are greater than 25.
MODIFY table PARTITION aa1 TO SPLIT WITH PARTITION = (RANGE ON aa PARTITION aa1a VALUES <= 25, PARTITION aa1b VALUES > 25)
21. Merge partitions p1, p2, and p3 of table1 into two partitions named new1 and new2:
MODIFY table1 PARTITION p1,p2,p3 TO MERGE WITH PARTITION=(RANGE ON pp PARTITION new1 VALUES <=10,PARTITION new2 VALUES <=20)
22. Drop partition aa1 of the employee table:
MODIFY employee PARTITION aa1 TO DROP
23. Repartition a table using the default partitioning count:
MODIFY sales_fact TO RECONSTRUCT WITH PARTITION=(HASH ON product_code DEFAULT PARTITIONS)