Personal tools
You are here: Home DB2 DB2 UDB v9 Advantages of using table partitioning
Log in

Forgot your password?
Document Actions

Advantages of using table partitioning

Easy roll-in and roll-out of data, Easier administration of large tables, Flexible index placement, Faster query processing



  • Easy roll-in and roll-out of data. Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to easily separate ranges of data from a partitioned table for subsequent purging or archiving. Data can be quickly rolled in and out by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement.

  • Easier administration of large tables. Table-level administration becomes more flexible because administrative tasks can be performed on individual data partitions. Such tasks include detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. In addition, time-consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can be performed at the data-partition level when each data partition is placed in a separate table space. Thus, it is possible to back up one data partition of a partitioned table at a time.

  • Flexible index placement. With table partitioning, indexes can be placed in different table spaces, allowing for more granular control of index placement.

  • Faster query processing. In the process of resolving queries, one or more data partitions may be automatically eliminated based on the query predicates used. This functionality, known as Partition Elimination, improves the performance of many queries because less data has to be analyzed before a result data set can be returned. In addition, queries can be automatically directed to just the partition(s) where the desired data resides. For example, if you partitioned a table based on month, and a query that is designed to calculate the total sales for March 2008 is executed, the query need only access the data for March 2008, not the data for other months and years.

  • Table partitioning improves performance by eliminating large amounts of I/O

Security Awareness
Would you like your company to implement gamification into your security awareness program?