Oracle Database 11g New Features for Table Partitioning

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

Range Partitioning

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.

Hash Partitioning

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.

Range-Hash Partitioning

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.

List Partitioning

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.

Range-List 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

Interval Partitioning

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);

System Partitioning

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'));

Reference Partitioning

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:

Range-Range
List-List
List-Hash
List-Range
Interval-Range
Interval-Hash
Interval-List

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.

»


See All Articles by Columnist
Karen Reliford