Oracle Database 11g New Features for Table Partitioning
September 1, 2010
Table partitioning is an option available to DBAs that can help them manage key database performance areas including i/o balancing, contention reduction, SQL statement performance improvements and even data availability. Oracle Database 11g adds some very powerful new table partitioning methods that can be implemented to help to achieve some of these goals.
Why Use Table Partitioning
There are several key areas that database administrators tend to focus on when looking for opportunities to improve performance in their databases. These include memory utilization, SQL performance, space and storage requirements, network bottlenecks, and i/o performance.
Considered part of the very large database support features, table partitioning is an option available to administrators that can help at least two of these key performance areas: i/o and SQL performance. By taking key large "hot" tables (and indexes) and partitioning them so that they are stored in different tablespaces, a dba may see performance gains in several areas.
1) Reduced contention for blocks from the tables or indexes. This results from having the blocks distributed among different tablespaces based on the partitioning options used.
2) Reduced I/O contention because the datafiles for the tablespaces used by partitioned tables can be physically placed on different devices or drives
3) SQL Performance improvement for statements that reference the partition key column in the where clause may result because the optimizer is able to use partition pruning (only reading from the specific partition(s) that match the data being requested).
4) Another possible benefit is availability of the data in the tables. Not only can administrative tasks be done while only taking a subset of partitions offline, if anything was to happen to only one datafile, the remaining partitions in other tablespaces would still be accessible.
With 11g, Oracle has added some very powerful new table partitioning methods that can be implemented to achieve these benefits for very large (and even small) database. However, before discussing the new table partitioning methods, lets do a quick review of Oracle's partitioning history.
Previously Released Partitioning Methods For Tables
Oracle introduced range partitioning back in Oracle Database 8.0 as it's first partitioning method. This was the ability to partition data based on ranges associated with a column in the table. It is most commonly used to divide the row data across different tablespaces based on date values. For example, a table containing sales data could be divided by quarter, month, year - or some other meaningful date range criteria.
This method was introduced in Oracle Database 8.1. With hash partitions, a table could be split into several partitions based on a key that did not really lend itself to an easy range division, such as a large customer table. We could create multiple partitions based on the customer id, and have Oracle evenly distribute the rows across the tablespaces based on the results of passing the partition column through a hashing algorithm and using that result to determine where to store the row.
Also added in 8i was the ability to do composite partitioning. This allowed to first partition the data based on ranges, and then within those ranges, to further partition into an equal number of hash partitions.
Introduced in 9i(R1), list partitioning filled a gap that was missing with the range and hash partitioning methods. List partitioning allows a table to be partitioned based on distinct values such as state codes (or province codes if you're in Canada), country codes or another key that had different discreet values, but didn't lend itself to range partitioning.
A new composite method of partitioning introduced in 9i(R2) enabled a table to be divided by ranges, and then sub-partitioned by a discreet list value.
New Table Partitioning Methods Introduced in Oracle Database 11g
Essentially, interval partitioning is an enhancement to range partitioning. One challenge with range partitions prior to 11g was that there was no way to have Oracle automatically create new partitions as data was added into the database. For example, if a table were being partitioned by month, at the start of a new month the database administrator would have to manually split the highest partition in order to separate data for the most recent month(s).
With interval partitioning, Oracle will automatically generate new partitions to accommodate new data being added into the table. When defining the partitioned table, the new "interval" option was added to the DDL command. Additionally, the use of "values less than maxvalue" was removed. Interval partitioning can only be done on date or number columns.
Here is an example of the syntax to create an orders table to be automatically interval partitioned on the order_date column by month.
CREATE TABLE orders_tbl (order_id number(10), order_date date, order_mode varchar2(10), order_total number(15,2) customer_id number(10)) PARTITION BY RANGE (order_date) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) STORE IN (data01,data02,data03,data04) (PARTITION JUN10 values less than TO_DATE('01-07-10','dd-mm-yy')), PARTITION JUL10 values less than TO_DATE('01-08-10','dd-mm-yy')), PARTITION AUG10 values less than TO_DATE('01-09-10','dd-mm-yy')));
Any data added into the specified ranges will be inserted as usual. As soon as any rows are added that go beyond the end of August, a new partition will be automatically added with a system generated name. You can query DBA_TAB_PARTITIONS to see the system generated partitions and names.
In addition, if you have an existing range partitioned table, you can quickly convert it to be an interval partitioned table using
ALTER TABLE table_name SET INTERVAL (interval value);
If the placement of data is going to be specifically managed by an application, and the only support required in the database is that the table needs to be broken down into smaller partitions, system partitioning may be the answer.
With system partitioning, there is no partition key, and some of the benefits such as partition pruning are not likely to be realized. In addition, some partition maintenance operations such as splitting partitions will not be supported.
CREATE TABLE mypart_tbl (id number, desc varchar2(50)) PARTITION BY SYSTEM (partition part_1 tablespace data01, partition part_2 tablespace data02, partition part_3 tablespace data03, partition part_4 tablespace data04);
Insert and merge statements into this table will require using the partition-extended syntax. Delete and update statements, however, do not require the extended command syntax.
INSERT INTO mypart_tab PARTITION part_1 VALUES (1,'Some Text');
Virtual Column Partitioning
Virtual columns that are calculated or derived from other data can now be used as the basis for partitioning a table. This allows a table to be partitioned based on business information that is not necessarily stored in individual columns.
For example, let's say a parts table has a column called part_id where the 3rd, 4th, and 5th characters represent the manufacturer code and management often requires reports and information for specific manufacturers. It might be beneficial to be able to partition the table based on the subset of characters.
CREATE TABLE parts_tbl (part_id char(10), desc varchar2(500), cost number(10,2) mfr_code as upper((substr(part_id,3,3))) PARTITION BY LIST(mfr_code) (PARTITION ABC_DEF VALUES ('ABC','DEF'), PARTITION GHI_JKL VALUES ('GHI','JKL'), PARTITION MNO_PQR VALUES ('MNO','PQR'), PARTITION STU_VWX VALUES ('STU','VWR'));
The last of the new table partitioning methods introduced in 11g is reference partitioning. This is a useful option for dealing with two tables in a one-to-many relationship such as orders and order items.
If the orders table is partitioned by range or interval based on the order date, it may make sense to also partition the order items table the same way. In the past, this would have required that we de-normalize the order items table by adding the order date to it so that we would be able to set up the table partition to match its parent table. One key benefit to reference partitioning is to not have the extra space being used, and not having to deal with the potential data integrity issues .
In order to use reference partitioning, a formal foreign key on the child table must be defined in the database and it will not be possible to disable the foreign key constraint.
CREATE TABLE orders_tbl (order_id number(10), order_date date, order_mode varchar2(10), order_total number(15,2), customer_id number(10)) PARTITION BY RANGE (order_date) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) STORE IN (data01,data02,data03,data04) (PARTITION JUN10 values less than TO_DATE('01-07-10','dd-mm-yy')), PARTITION JUL10 values less than TO_DATE('01-08-10','dd-mm-yy')), PARTITION AUG10 values less than TO_DATE('01-09-10','dd-mm-yy'))); CREATE TABLE order_items_tbl (order_id NUMBER(10), line_id NUMBER(3), product_id NUMBER(10), price NUMBER(10,2), quantity NUMBER(5), CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders_tbl) PARTITION BY REFERENCE (order_items_fk);
Additional composite partitioning options were also added in Oracle 11g that now include the four new partitioning methods. So, we can now use the following combinations:
Examples of the syntax for many of these combinations can be found at http://download.oracle.com/docs/cd/E11882_01/server.112/e10837/part_avail.htm#insertedID5
With the addition of these new table partitioning methods in Oracle Database 11g, database administrators have some interesting and potentially powerful new options to consider when managing large and/or very hot table(s) in their databases.