 PMAN -- Partition Manager for Oracle Range Partitions
Michael Wang and Julie Wang
Oracle table partitioning divides a large table into smaller more manageable units, called partitions. It is a simple "divide and conquer" concept, but it offers tremendous benefits. Partitioning greatly increases the database management capability. Each partition can be individually managed; it can be dropped, truncated, moved, split, merged, and exchanged with a table.
Although each partition can be individually queried, you do not have to change your SQL to take advantage of the partitioning. The "partition pruning" is done transparently via Oracle optimizer. Partitioning, together with locally managed and uniform extent sizes, permanently solved the problem of data fragmentation caused by data constantly moving in and out of the table. The partitions that are dropped or truncated release the entire space occupied to the residing tablespace. Admins who have spent endless hours performing object reorganization will appreciate the beauty of this feature.
Basically, there are four types of partitions:
- Range partition -- For continuous values like date and number.
- List partition -- For discrete values like U.S. states.
- Hash partition -- For random values like license plates.
- Composite partition -- A partition within a partition.
This article presents the PMAN utility, which handles the range partition based on the date column, which is the most common type of partitioning. The date can be presented by Oracle internal date type, timestamp type, or by number type -- for example, 20070130 for January 30, 2007, or character type such as Ô20070130'.
What PMAN Does
PMAN manages the partitions through out the life cycle. It can help you create partitions when you want to convert a non-partitioned table containing many years of data to a partitioned table. PMAN also helps you manage the partitions once you have a partitioned table. It creates new partitions, drops old partitions per your specifications, updates statistics, and validates indexes.
PMAN can also help you merge daily partitions into monthly partitions, and monthly partitions into yearly partitions, and drop the merged (or archived) partitions when you do not need them.
How PMAN Does It
PMAN needs to know how the table on which it operates is partitioned. It could query the dictionary and get an idea, but it is easier and more reliable just to look at the label on the partition. Each partition needs to have a name to identify the partition. Oracle does not care how you name it as long as you name it. Users usually don't need to know what the name is or even that the table is partitioned as long as their queries continue to run.
So, here are PMAN's naming standard:
partition_name partitioned by
============== =============================================================
Y2004 YEAR with DATE/TIMESTAMP type
M200407 MONTH with DATE/TIMESTAMP type
D20040704 DAY with DATE/TIMESTAMP type
W20040705 WEEK with DATE/TIMESTAMP type
P2004 YEAR with NUMBER/CHAR type with date represented as YYYY
P200400 YEAR with NUMBER/CHAR type with date represented as YYYYMM
P20040000 YEAR with NUMBER/CHAR type with date represented as YYYYMMDD
P200407 MONTH with NUMBER/CHAR type with date represented as YYYYMM
P20040700 MONTH with NUMBER/CHAR type with date represented as YYYYMMDD
P20040704 DAY with NUMBER/CHAR type with date represented as YYYYMMDD
PW20040705 WEEK with NUMBER/CHAR type with date represented as YYYYMMDD
============== =============================================================
Standards are good as long as they are easy to follow and do not limit the application in any way. With this particular naming standard, it is quite easy to find how the table is partitioned, how to add new partitions, and drop or merge old partitions, etc.
PMAN Features
We will go through the life cycle of partition management with an example, and demonstrate the PMAN usage, features, and design decisions along the way.
1. Create Initial Partitions
To start a new partitioned table, just create the table with the earliest partition manually, like this:
SQL> CREATE TABLE trade.settlement (
2 settle_id NUMBER,
3 settle_date DATE
4 )
5 PARTITION BY RANGE (settle_date)
6 (PARTITION M200509 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> ALTER TABLE TRADE.SETTLEMENT
2> ADD CONSTRAINT pk_settlement PRIMARY KEY (settle_id);
Table altered.
And let the PMAN takes care of the rest.
Note that we created the last partition to be able to hold any future rows without an upper bound. This was a conscious decision for added reliability. In case you forget to create future partitions, all rows will go to the last partition. This is not an ideal situation but may be better than failure.
Next, we use PMAN to create the rest of the partitions. For example, the following command will create two more partitions:
$ pman sid:=$ORACLE_SID table=trade.settlement next=2 show=n
The result is:
SQL> ALTER TABLE TRADE.SETTLEMENT
2 SPLIT PARTITION M200509
3 AT (to_date(20051001, 'YYYYMMDD'))
4 INTO (PARTITION M200509, PARTITION M200510)
5 UPDATE GLOBAL INDEXES
6 ;
Table altered.
SQL> ALTER TABLE TRADE.SETTLEMENT
2 SPLIT PARTITION M200510
3 AT (to_date(20051101, 'YYYYMMDD'))
4 INTO (PARTITION M200510, PARTITION M200511)
5 UPDATE GLOBAL INDEXES
6 ;
Table altered.
Please note the following:
- RMAN uses the "SPLIT PARTITION" statement to split two partitions as specified.
- The partition column is only needed once when you create the partitioned table. It is not needed for subsequent management. In other words, the partition column name is not a programming parameter and need not to be maintained outside the database.
- The "show=y" option will produce the SQL statements without any changes on the database. This is useful if you want to test PMAN or use PMAN as a SQL generator. It is tedious and error prone work to manually create the SQL code to create many partitions, say, monthly partitions for the past 7 years. You can leave this job to PMAN.
- The "next=<n>" options create the next <n> partitions relative to the CURRENT partitions, which is the partition with the current date.
We start with one partition, M200509, which is the current partition for any date. The "next=2" option creates two more partitions on top of the M200509 partition, ending up with three partitions: M200509, M200510, M200511.
2. Maintain Future Partitions
Now suppose today is 10/15/2005, the current partition is M200510. If you want to pre-create three future partitions, you should use the option "next=3". The PMAN command is:
$ pman sid:=$ORACLE_SID table=trade.settlement next=3 show=n \
today=20051015
Since there is already a future partition M200511, PMAN will create just two more partitions. In other words, PMAN will create as many partitions necessary to maintain the number of future partitions required. However, PMAN will not drop future partitions if the future partitions are more than what you need.
If it was indeed October 15, 2005, or any day in October 2005 for this example, you do not need to use the <today=YYYYMMDD> option. This option is needed only when you pretend today is a different date.
3. Maintain Past Partitions
While the "next=<n>" option is the number of partitions you would like to maintain after the current partition, the "keep=<n>" is the number of partitions you would like to maintain before the current partition.
The PMAN command to drop all old partitions except the three most recent ones is:
$ pman sid:=$ORACLE_SID table=trade.settlement keep=3 show=n \
today=20060115
The result is:
SQL> SELECT 'partition='||partition_name
2 FROM dba_tab_partitions
3 WHERE table_owner='TRADE'
4 AND table_name='SETTLEMENT'
5 ORDER BY 1;
'PARTITION='||PARTITION_NAME
----------------------------------------
partition=M200509
partition=M200510
partition=M200511
partition=M200512
partition=M200601
SQL> SELECT 'version=' || VERSION from v$instance;
'VERSION='||VERSION
-------------------------
version=10.1.0.5.0
SQL> ALTER TABLE TRADE.SETTLEMENT
2 DROP PARTITION M200509
3 UPDATE GLOBAL INDEXES
4 ;
Table altered.
We see that PMAN first lists all the partitions in the chronological order. Since today is January 15, 2006, M200601 is the current partition, and M200509 through M200512 are the past four partitions. Since we only want to keep 3 month's worth of data, the oldest partition, M200509, is dropped.
We see also that Oracle version is queried. This is because when you drop the partitions with data, most likely the global indexes will become invalid. Oracle versions 9i and 10g provide the "UPDATE GLOBAL INDEXES" clause to update the indexes simultaneously.
If you are using Oracle database version 8i or below, additional SQL statements like the following will be generated and executed:
ALTER INDEX <owner>.<index> REBUILD COMPUTE STATISTICS;
If you specify the option "keep=<n>T" instead of "keep=<n>", PMAN will truncate the unwanted partitions instead of dropping them. This could be useful if the table structure needs to be maintained or a future restore is needed.
The keep and next options can also be used at the same time. This is how jobs are normally set up -- to drop one old partition and pre-create a new partition each month for monthly partitioned tables. We separated these two options for illustration purposes.
4. Merge Past Partitions
The merge option will merge previous monthly partitions into yearly partitions, and previous daily partitions into monthly partitions. In our example, the following three partitions from the previous year (relative to the current partition):
M200510
M200511
M200512
will merge to A2005, where A stands for Archive.
The PMAN command is:
$ pman sid:=$ORACLE_SID table=trade.settlement merge=y show=n \
today=20060115
The result is:
SQL> ALTER TABLE TRADE.SETTLEMENT MERGE PARTITIONS
2 M200510, M200511
3 INTO PARTITION
4 M200511a
5 UPDATE GLOBAL INDEXES
6 ;
Table altered.
SQL> ALTER TABLE TRADE.SETTLEMENT MERGE PARTITIONS
2 M200511a, M200512
3 INTO PARTITION
4 M200512a
5 UPDATE GLOBAL INDEXES
6 ;
Table altered.
SQL> ALTER TABLE TRADE.SETTLEMENT RENAME PARTITION M200512a
2 to A2005;
Table altered.
5. Drop Merged (Archived) Partitions
The archive option is just like the keep option, but it operates on the merged (archived) partitions.
To illustrate this feature, let's fast-forward the clock one year; for example, let's say you created all 2006 partitions and archived them.
The following PMAN commands simulate this process:
$ pman sid:=$ORACLE_SID table=trade.settlement next=12 show=n \
today=20060115
$ pman sid:=$ORACLE_SID table=trade.settlement merge=y show=n \
today=20070115
The first command will create 12 partitions, bringing the last partition to M200701, and the second command will merge all 2006 partitions.
Now we have two archived partitions, A2005 and A2006. Suppose your site policy is to keep the data for 1 year in addition to the current year; you can then use the archive option to drop all archived partitions except the most recent one.
The PMAN command to do this is:
$ pman sid:=$ORACLE_SID table=trade.settlement archive=1 show=n \
today=20070115
which simply does:
SQL> ALTER TABLE TRADE.SETTLEMENT
2 DROP PARTITION A2005
3 UPDATE GLOBAL INDEXES
4 ;
Table altered.
The "today" option would be useful if you needed to merge 2006 partitions in the year 2008. You do not need to use the option if you run the command in year 2007.
Oracle Considerations
Statistics
PMAN also gathers statistics for the partitions it touches, including split partitions, and merged partitions. For example:
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (
3 OWNNAME => 'TRADE',
4 TABNAME => 'SETTLEMENT',
5 PARTNAME => 'M200511',
6 GRANULARITY => 'PARTITION',
7 CASCADE => TRUE,
8 ESTIMATE_PERCENT => 20
9 );
10 END;
11 /
In a normal situation, the last partition is a future partition and is empty. The newly split partitions are therefore empty as well. But zero statistics are still meaningful.
Indexes
PMAN generates statements to rebuild local indexes for the partitions it touched:
SQL> ALTER TABLE TRADE.SETTLEMENT MODIFY PARTITION M200511
2 REBUILD UNUSABLE LOCAL INDEXES;
In normal situation, we split empty partitions, which will not make local indexes unusable. PMAN also generates statements to rebuild global indexes for Oracle 8i and below, and uses "UPDATE GLOBAL INDEXES" for Oracle 9i and above.
Tablespaces
One of the advantages of partitioning is that we can put different partitions on different disks for performance and capacity planning. PMAN is designed to maintain a constant number of partitions occupying a constant or slowly growing amount of disk space. It does not use the TABLESPACE clause for partitions. As a result, the split table partitions and index partitions remain in the same tablespaces where they were split out, respectively.
You can expand this usage by periodically moving the last partitions to new tablespaces, by adding the TABLESPACE clause to put January partitions on January tablespace, February partitions on January tablespace, etc.
Functional Bitmap Index
Typically, the last partition should be empty anyway. It is more important to keep it that way if you are running Oracle prior to Oracle 9.0.1 and have a functional bitmap index. This is because when we split a non-empty partition, the functional bitmap index (and all other indexes) will become invalid.
Rebuilding the functional bitmap index for a partition will cause the entire table to be scanned due to the Oracle bug 1987514. Depending on the size of the table, this could be a problem. So, in this case, it is better to drop the index and recreate it. The bug involves the functional local index in general (the bitmap index is always local), but we only have experience with the functional bitmap index. The bug is supposedly fixed in Oracle 9.0.1.
Portability
PMAN is written in portable shell, which runs on any platform with ksh or bash. It has been tested on various combinations of shell (ksh, bash), Oracle database versions (8i, 9i, and 10g), and operating environments (Solaris, HPUX, AIX, and Cygwin).
Caveats
Although the PMAN naming standard does not pose any limitation on the use of partitions, as stated previously, there could a problem if your organization uses a different standard. There are two solutions for this problem if you want to use PMAN. You could change the partition name to the PMAN standard if the partition name is not an externally exposed interface, or you could change the PMAN name to fit your existing standard. If your naming standard does not include information about how the table is partitioned (e.g., yearly, monthly, etc.), then you will have to modify the program to accept the information.
It is certainly possible to have a generic program without using the naming convention, just as it is possible, but more difficult, to do electrical wiring without a color-coding standard. However, unless you opt to randomly generate partition names, you have to have a naming convention anyway. It is just a different standard. The naming standard is not really a technical issue. Like a cultural difference, it may cause confusion, but it also creates opportunities and makes life more interesting.
Summary
PMAN can help you create partitioned tables or convert regular tables to partitioned tables as well as manage the partitions. It can also be used as a SQL generator, if desired. PMAN is simple, generic, portable, and reliable.
Acknowledgements
Version 1.0 (2001-12-18) of PMAN was inspired by our friend David Xiao's PL/SQL code. Throughout the years, enhancements have been made and bugs fixed with the help of friends and users. The current version 4.5 (2007-03-17) of PMAN (Listing 1) and future updates are available from the Sys Admin Web site:
http://www.sysadminmag.com
or from:
http://www.unixlabplus.com/unix-prog/pman
References
Wang, Michael and Julie Wang. "Date-Related Shell Functions." September 2005. UnixReview.com -- http://www.unixreview.com/documents/s=9521/ur0509a/
Oracle Corporation. "Partitioned Tables and Indexes". Oracle Database Concepts. October, 2005. -- http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220.pdf
Michael Wang has been a sys admin, Oracle database admin, and is now a Unix programmer. He is an Oracle 10g certified professional. He can be reached at: xw73@columbia.edu.
Julie Wang has managed Unix systems and Lawson Enterprise Systems, and currently manages Oracle databases. She is an Oracle 10g certified professional and can be reached at: julie_wangye@yahoo.com.
|