Reduced Administrative Burden-Partitioning-1
The administrative burden relief is derived from the fact that performing operations on small objects is inherently easier, faster, and less resource intensive than performing the same operation on a large object.
For example, say you have a 10GB index in your database. If you need to rebuild this index and it is not partitioned, then you will have to rebuild the entire 10GB index as a single unit of work.
While it is true that you could rebuild the index online, it requires a huge number of resources to completely rebuild an entire 10GB index. You’ll need at least 10GB of free storage elsewhere to hold a copy of both indexes, you’ll need a temporary transaction log table to record the changes made against the base table during the time you spend rebuilding the index, and so on.
On the other hand, if the index itself had been partitioned into ten 1GB partitions, then you could rebuild each index partition individually, one by one. Now you need ten percent of the free space you needed previously. Likewise, the individual index rebuilds will each be much faster (ten times faster, perhaps), so far fewer transactional changes occurring during an online index rebuild need to be merged into the new index, and so on.
Also, consider what happens in the event of a system or software failure just before completing the rebuilding of a 10GB index. The entire effort is lost. By breaking the problem down and partitioning the index into 1GB partitions, at most you would lose ten percent of the total work required to rebuild the index.
Last, but not least, it may be that you need to rebuild only ten percent of the total aggregate index—for example, only the “newest” data (the active data) is subject to this reorganization, and all of the “older” data (which is relatively static) remains unaffected.
Another example could be that you discover 50 percent of the rows in your table are “migrated” rows (see Chapter 10 for details on migrated rows), and you would like to fix this. Having a partitioned table will facilitate the operation.
To “fix” migrated rows, you must typically rebuild the object—in this case, a table. If you have one 100GB table, you will need to perform this operation in one very large chunk serially, using ALTER TABLE MOVE. On the other hand, if you have 25 partitions, each 4GB in size, then you can rebuild each partition one by one.
Alternatively, if you are doing this during off-hours and have ample resources, you can even do the ALTER TABLE MOVE statements in parallel, in separate sessions, potentially reducing the amount of time the whole operation will take.
Virtually everything you can do to a nonpartitioned object, you can do to an individual partition of a partitioned object. You might even discover that your migrated rows are concentrated in a very small subset of your partitions; hence, you could rebuild one or two partitions instead of the entire table.
Here is a quick example demonstrating the rebuild of a table with many migrated rows. Both BIG_TABLE1 and BIG_TABLE2 were created from a 10,000,000-row instance of BIG_TABLE (see the “Setting Up Your Environment” at the beginning of the book for the BIG_TABLE creation script). BIG_TABLE1 is a regular, nonpartitioned table, whereas BIG_TABLE2 is a hash partitioned table in eight partitions (we’ll cover hash partitioning in detail in a subsequent section; suffice it to say, it distributed the data rather evenly into eight partitions).
This example creates two tablespaces and then creates the two tables:
SQL> create tablespace big1 datafile size 1500M; Tablespace created.
SQL> create tablespace big2 datafile size 1500m; Tablespace created.
SQL> create table big_table1
Now, each of those tables is in its own tablespace, so we can easily query the data dictionary to see the allocated and free space in each tablespace:
SQL> select b.tablespace_name,mbytes_alloc,mbytes_free
BIG1 and BIG2 are about 1500MB in size and each about 219MB of free space. We’ll try to rebuild the first table, BIG_TABLE1:
SQL> alter table big_table1 move;alter table big_table1 move
ERROR at line 1:
ORA-01652: unable to extend temp segment by 1024 in tablespace BIG1