Online reorganization and redefinition of relational database tables
Techniques are provided for allowing the reorganization and/or redefinition of data in a relational database with minimal or no downtime by allowing “online” reorganization/redefinition of the data. According to one aspect of the invention, an empty revised table is created in the same schema as the target table. The revised table is created with the attributes that are desired for the reorganization/redefinition of the target table. Triggers, constraints, grants and indexes that are associated with the target table are replicated for the revised table. The reorganization/redefinition is performed by using an underlying mechanism that involves using incrementally maintainable materialized views. According to one feature, the revised table is initialized and periodically synchronized with the target table. After the reorganization and/or redefinition is complete on the revised table, the revised table and the target table switch identities such that the revised table is now the target table and the target table becomes the revised table.
1. A method for modifying a target table within a relational database, the method comprising the steps of:
creating a revised table that has one or more attributes that are different than corresponding attributes of the target table;
while the revised table is being created preventing user access to the revised table while allowing user access to the target table;
when creation of the revised table is complete, performing the steps of:
locking the target table;
synchronizing the revised table with the target table; and
causing all subsequent attempts to access the target table to access the revised table.
2. The method of claim 1 , wherein:
the step of creating the revised table involves creating the revised table having a first name and wherein the target table has a second name; and
the step of causing all subsequent attempts to access the target table to access the revised table involves assigning to the revised table the second name and assigning to the target table the first name.
3. The method of claim 1 , further comprising providing column mapping information between the target table and the revised table.
4. The method of claim 1 , wherein the one or more attributes that are different than corresponding attributes of the target table include at least one of:
storage parameters that are associated with the revised table;
re-location to a tablespace that is different than a tablespace that is associated with the target table;
renaming columns that are copied from the target table;
adding new columns to the revised table; and
transforming data one or more columns of the revised table.
5. The method of claim 1 , further comprising the step of creating for the revised table any grants, triggers, constraints and indexes that correspond to the target table.
6. The method of claim 5 , wherein the grants, triggers, constraints and indexes that are created for the revised table are disabled while the revised table is being created.
7. The method of claim 5 , wherein the grants, triggers, constraints and indexes that are created for the revised table are enabled when the creation of the revised table is complete.
8. The method of claim 5 , wherein the corresponding grants, triggers, constraints and indexes of the target table are disabled when the creation of the revised table is complete.
9. The method of claim 1 wherein:
the step of creating a revised table that has one or more attributes that are different than corresponding attributes of the target table includes creating a plurality of revised tables;
while the plurality of revised tables are being created preventing user access to the plurality of revised tables while allowing user access to the target table;
when creation of the revised tables is complete, performing the steps of:
locking the target table;
synchronizing the plurality of revised tables with the target table; and
causing all subsequent attempts to access the target table to access one or more of the revised tables.
10. The method of claim 1 wherein:
the step of creating a revised table that has one or more attributes that are different than corresponding attributes of the target table includes creating a plurality of revised tables based on a plurality of target tables;
while the plurality of revised tables are being created, preventing user access to the plurality of revised tables while allowing user access to the plurality of target tables;
when creation of the revised tables is complete, performing the steps of:
locking the plurality of target tables;
synchronizing the plurality of revised tables with the plurality of target tables; and
causing all subsequent attempts to access at least one of the plurality of target tables to access one or more of the revised tables.
11. The method of claim 1 wherein:
the step of creating a revised table that has one or more attributes that are different than corresponding attributes of the target table includes creating a revised table based on a plurality of target tables;
while the revised table is being created, preventing user access to the revised table while allowing user access to the plurality of target tables;
when creation of the revised table is complete, performing the steps of:
locking the plurality of target tables;
synchronizing the revised table with the plurality of target tables; and
causing all subsequent attempts to access at least one of the plurality of target tables to access the revised table.
12. A computer-readable medium carrying instructions for modifying a target table within a relational database, the instructions comprising instructions for performing the steps of:
creating a revised table that has one or more attributes that are different than corresponding attributes of the target table;
while the revised table is being created preventing user access to the revised table while allowing user access to the target table;
when creation of the revised table is complete, performing the steps of:
locking the target table;
synchronizing the revised table with the target table; and
causing all subsequent attempts to access the target table to access the revised table.
13. The computer-readable medium of claim 12 , wherein:
the step of creating the revised table involves creating the revised table having a first name and wherein the target table has a second name; and
the step of causing all subsequent attempts to access the target table to access the revised table involves assigning to the revised table the second name and assigning to the target table the first name.
14. The computer-readable medium of claim 12 , further comprising instructions for providing column mapping information between the target table and the revised table.
15. The computer-readable medium of claim 12 , wherein the one or more attributes that are different than corresponding attributes of the target table include at least one of:
storage parameters that are associated with the revised table;
re-location to a tablespace that is different than a tablespace that is associated with the target table;
renaming columns that are copied from the target table;
adding new columns to the revised table; and
transforming data one or more columns of the revised table.
16. The computer-readable medium of claim 12 , further comprising instructions for performing the step of creating for the revised table any grants, triggers, constraints and indexes that correspond to the target table.
17. The computer-readable medium of claim 16 , wherein the grants, triggers, constraints and indexes that are created for the revised table are disabled while the revised table is being created.
18. The computer-readable medium of claim 16 , wherein the grants, triggers, constraints and indexes that are created for the revised table are enabled when the creation of the revised table is complete.
19. The computer-readable medium of claim 16 , wherein the corresponding grants, triggers, constraints and indexes of the target table are disabled when the creation of the revised table is complete.
20. The computer-readable medium of claim 12 wherein:
the step of creating a revised table that has one or more attributes that are different than corresponding attributes of the target table includes creating a plurality of revised tables;
while the plurality of revised tables are being created preventing user access to the plurality of revised tables while allowing user access to the target table;
when creation of the revised tables is complete, performing the steps of:
locking the target table;
synchronizing the plurality of revised tables with the target table; and
causing all subsequent attempts to access the target table to access one or more of the revised tables.
21. The computer-readable medium of claim 12 wherein:
the step of creating a revised table that has one or more attributes that are different than corresponding attributes of the target table includes creating a plurality of revised tables based on a plurality of target tables;
while the plurality of revised tables are being created, preventing user access to the plurality of revised tables while allowing user access to the plurality of target tables;
when creation of the revised tables is complete, performing the steps of:
locking the plurality of target tables;
synchronizing the plurality of revised tables with the plurality of target tables; and
causing all subsequent attempts to access at least one of the plurality of target tables to access one or more of the revised tables.
22. The computer-readable medium of claim 12 wherein:
the step of creating a revised table that has one or more attributes that are different than corresponding attributes of the target table includes creating a revised table based on a plurality of target tables;
while the revised table is being created, preventing user access to the revised table while allowing user access to the plurality of target tables;
when creation of the revised table is complete, performing the steps of:
locking the plurality of target tables;
synchronizing the revised table with the plurality of target tables; and
causing all subsequent attempts to access at least one of the plurality of target tables to access the revised table.