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. X100: Insert into table "tab" data from the staging table "tab_insertions":
MODIFY tab UNION tab_insertions TO COMBINE
21. X100: Replace all data in table1 with data from table2:
MODIFY table1 EXCEPT table1 UNION table2 TO COMBINE
22. X100: Keep track of changes in the current product range by creating an intermediate change table:
DROP IF EXISTS product_master;
CREATE TABLE product_master (id_pm INTEGER, name_pm VARCHAR(30), intro_pm DATE);
DROP IF EXISTS product_current;
CREATE TABLE product_current (id_pc INTEGER, name_pc VARCHAR(30));
DROP IF EXISTS change_p;
CREATE TABLE change_p (id_pm INTEGER, name_pm VARCHAR(30), intro_pm DATE);
INSERT INTO product_current VALUES (1, 'Orange Juice'), (2, 'Grape Juice'),(3, 'Grapefruit Juice'),(4, 'Apple Juice'),(5, 'Cranberry Juice'),(6, 'Strawberry Juice'),(7, 'Mango Juice');
SELECT * FROM product_current;
INSERT INTO change_p SELECT pc.id_pc AS id_pm, name_pc AS name_pm, DATE'2011-10-14' AS intro_pm
FROM product_current pc WHERE id_pc NOT IN (SELECT id_pm FROM product_master);
COMMIT;
MODIFY product_master EXCEPT change_p UNION change_p TO COMBINE;
SELECT * FROM product_master;
COMMIT;
INSERT INTO product_current VALUES (8, 'Gooseberry Juice');
UPDATE product_current SET name_pc = 'Carrot Juice' WHERE id_pc = 1;
COMMIT;
SELECT * FROM product_current;
MODIFY change_p EXCEPT change_p TO COMBINE;
INSERT INTO change_p select pc.*, CURRENT_DATE FROM product_current pc
WHERE id_pc NOT IN (SELECT id_pm FROM product_master WHERE name_pc = name_pm);
COMMIT;
SELECT * FROM change_p;
MODIFY product_master EXCEPT change_p UNION change_p TO COMBINE;
SELECT * FROM product_master;
23. 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)
24. 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)
25. Drop partition aa1 of the employee table:
MODIFY employee PARTITION aa1 TO DROP
26. Repartition a table using the default partitioning count:
MODIFY sales_fact TO RECONSTRUCT WITH PARTITION=(HASH ON product_code DEFAULT PARTITIONS)