Table partitioning is a data organization scheme in which
table data is divided across multiple storage objects called data
partitions according to values in one or more table columns.
Each data partition is stored separately. These storage objects can
be in different table spaces, in the same table space, or a combination
of both.
Storage objects behave much like individual tables, making it easy
to accomplish fast roll-in by incorporating an existing table into
a partitioned table using the ALTER TABLE ... ATTACH statement. Likewise,
easy roll-out is accomplished with the ALTER TABLE ... DETACH statement.
Query processing can also take advantage of the separation of the
data to avoid scanning irrelevant data, resulting in better query
performance for many data warehouse style queries.
Table data is partitioned as specified in the PARTITION BY clause
of the CREATE TABLE statement. The columns used in this definition
are referred to as the table partitioning key columns.
This organization scheme can be used in isolation or in combination
with other organization schemes. By combining the DISTRIBUTE BY and
PARTITION BY clauses of the CREATE TABLE statement, data can be spread
across database partitions spanning multiple table spaces. The organization
schemes include:
- DISTRIBUTE BY HASH
- PARTITION BY RANGE
- ORGANIZE BY DIMENSIONS
Table partitioning is available with the DB2® Version 9.1 Enterprise Server Edition for Linux, UNIX, and Windows,
and later.
Benefits of table partitioning
If any of
the following circumstances apply to you and your organization, consider
the numerous benefits of table partitioning:
- You have a data warehouse that would benefit from easier roll-in
and roll-out of table data.
- You have a data warehouse that includes large tables.
- You are considering a migration to a Version 9.1 database from
a previous release or a competitive database product.
- You want to use hierarchical storage management (HSM) solutions
more effectively.
Table partitioning offers easy roll-in and roll-out of
table data, easier administration, flexible index placement, and better
query processing.
- Efficient roll-in and roll-out
- Table partitioning allows for the efficient roll-in and roll-out
of table data. You can achieve this efficiency by using the ATTACH
PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement.
Rolling in partitioned table data allows a new range to be easily
incorporated into a partitioned table as an additional data partition.
By rolling out partitioned table data, you can easily separate ranges
of data from a partitioned table for subsequent purging or archiving.
With DB2 Version
9.7 Fix Pack 1 and later releases, when detaching a data partition
from a partitioned table by using the ALTER TABLE statement with the
DETACH PARTITION clause, the source partitioned table remains accessible
to dynamic queries that run under the RS, CS, or UR isolation level.
Similarly, when attaching a data partition to a partitioned table
by using the ALTER TABLE statement with the ATTACH PARTITION clause,
the target partitioned table remains accessible to dynamic queries
that run under the RS, CS, or UR isolation level.
- Easier administration of large tables
- Table level administration is more flexible because you can perform
administrative tasks on individual data partitions. These tasks include:
detaching and reattaching of a data partition, backing up and restoring
individual data partitions, and reorganizing individual indexes. Time
consuming maintenance operations can be shortened by breaking them
down into a series of smaller operations. For example, backup operations
can work data partition by data partition when the data partitions
are placed in separate table spaces. Thus, it is possible to back
up one data partition of a partitioned table at a time.
- Flexible index placement
- Indexes can now be placed in different table spaces, allowing
for more granular control of index placement. Some benefits of this
design include:
- Improved performance when dropping indexes and during online index
creation.
- The ability to use different values for any of the table space
characteristics between each index on the table (for example, different
page sizes for each index might be appropriate to ensure better space
utilization).
- Reduced I/O contention that provides more efficient concurrent
access to the index data for the table.
- When individual indexes are dropped, space immediately becomes
available to the system without the need for index reorganization.
- If you choose to perform index reorganization, an individual index
can be reorganized.
Both DMS and SMS table spaces support the use of indexes in
a different location than the table.
- Improved performance for business intelligence style queries
- Query processing is enhanced to automatically eliminate data partitions
based on predicates of the query. This query processing is known as data
partition elimination, and can benefit many decision support
queries.
The following example creates a table named CUSTOMER,
where rows with l_shipdate >= '01/01/2006' and l_shipdate <=
'03/31/2006' are stored in table space TS1, rows with l_shipdate >=
'04/01/2006' and l_shipdate <= '06/30/2006' are stored in table
space TS2, and so on.
CREATE TABLE customer (l_shipdate DATE, l_name CHAR(30))
IN ts1, ts2, ts3, ts4, ts5
PARTITION BY RANGE(l_shipdate) (STARTING FROM ('01/01/2006')
ENDING AT ('12/31/2006') EVERY (3 MONTHS))