Reduced Administrative Burden-Partitioning-2
This fails—we need sufficient free space in tablespace BIG1 to hold an entire copy of BIG_TABLE1 at the same time as the old copy is there—in short, we need about two times the storage for a short period (maybe more, maybe less—it depends on the resulting size of the rebuilt table). We now attempt the same operation on BIG_TABLE2:
SQL> alter table big_table2 move;alter table big_table2 move
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
This is Oracle telling us we can’t do the MOVE operation on the table; we must perform the operation on each partition of the table instead. We can move (hence rebuild and reorganize) each partition one by one:
SQL> alter table big_table2 move partition part_1;Table altered.
SQL> alter table big_table2 move partition part_2;Table altered.
SQL> alter table big_table2 move partition part_3;Table altered.
Each individual move only needs sufficient free space to hold a copy of one-eighth of the data! Therefore, these commands succeed given the same amount of free space as we had before.
We need significantly less temporary resources, and, further, if the system fails (e.g., due to a power outage) after we move PART_4 but before PART_5 finished moving, we won’t lose all of the work performed. The first four partitions would still be moved when the system recovers, and we may resume processing at partition PART_5.
Some may look at that and say, “Wow, eight statements—that is a lot of typing,” and it’s true that this sort of thing would be unreasonable if you had hundreds of partitions (or more). Fortunately, it is very easy to script a solution, and the previous would become simply
All of the information you need is there in the Oracle data dictionary, and most sites that have implemented partitioning also have a series of stored procedures they use to make managing large numbers of partitions easy.
Additionally, many GUI tools such as Enterprise Manager have the built-in capability to perform these operations as well, without your needing to type in the individual commands.
Another factor to consider with regard to partitions and administration is the use of sliding windows of data in data warehousing and archiving. In many cases, you need to keep data online that spans the last N units of time.
For example, say you need to keep the last 12 months or the last 5 years online. Without partitions, this is generally a massive INSERT followed by a massive DELETE, resulting in two massive transactions. Lots of DML, and lots of redo and undo generated.
Now with partitions, you can simply do the following:
\ 1.\ Load a separate table with the new months’ (or years’, or whatever) data.
\ 2.\ Index the table fully. (These steps could even be done in another instance and transported to this database).
\ 3.\ Attach this newly loaded and indexed table onto the end of the partitioned table using a fast DDL command: ALTER TABLE EXCHANGE PARTITION.
\ 4.\ Detach the oldest partition off the other end of the partitioned table.
So, you can now very easily support extremely large objects containing time- sensitive information. The old data can easily be removed from the partitioned table and simply dropped if you do not need it, or it can be archived off elsewhere. New data can be loaded into a separate table, so as to not affect the partitioned table until the loading, indexing, and so on is complete. We will take a look at a complete example of a sliding window later.
In short, partitioning can make what would otherwise be daunting, or in some cases unfeasible, operations as easy as they are in a small database.