Designing Tables and Indexes for Performance › Table Designs for Special Situations › UNION in View for Large Table Design

UNION in View for Large Table Design

The amount of data being stored in DB2 is increasing dramatically. Availability of databases is also an increasingly important issue. As you build these giant tables in your system, you must help ensure that they are built in a way that they are available 24-hours a day, 7-days per week. Large tables must be easy to access, hold significant quantities of data, and easy to manage.

Traditionally, larger database tables have been placed into partitioned table spaces. Partitioning helps with database management because it is easier to manage several small objects versus one very large object. Some limits to partitioning exist. For example, each partition is limited to a maximum size of 64 GB, a partitioning index is required (DB2 V7 only), and if efficient alternate access paths to the data are desired, then non-partitioning secondary indexes (NPSIs) are required. These NPSIs are not partitioned, and exist as single large database indexes. Thus, NPSIs can present themselves as an obstacle to availability (that is, a utility operation against a single partition may potentially make the entire NPSI unavailable), and as an impairment to database management because it is more difficult to manage large database objects.

You can use a UNION in a view as an alternative to table partitioning in support of very large database tables. In this type of design, several database tables can be created to hold different subsets of the data that would have otherwise been held in a single table. Key values, similar to what may be used in partitioning, can be used to determine which data goes into which of the various tables. For example, the following view definition:

CREATE VIEW V_ACCOUNT_HISTORY
   (ACCOUNT_ID, PAYMENT_DATE, PAYMENT_AMOUNT,
    PAYMENT_TYPE, INVOICE_NUMBER)
AS
SELECT ACCOUNT_ID, PAYMENT_DATE, PAYMENT_AMOUNT,
       PAYMENT_TYPE, INVOICE_NUMBER
FROM   ACCOUNT_HISTORY1
WHERE  ACCOUNT_ID BETWEEN 1 AND 100000000
UNION ALL
SELECT ACCOUNT_ID, PAYMENT_DATE, PAYMENT_AMOUNT,
       PAYMENT_TYPE, INVOICE_NUMBER
FROM   ACCOUNT_HISTORY2
WHERE  ACCOUNT_ID BETWEEN 100000001 AND 200000000
UNION ALL
SELECT ACCOUNT_ID, PAYMENT_DATE, PAYMENT_AMOUNT,
       PAYMENT_TYPE, INVOICE_NUMBER
FROM   ACCOUNT_HISTORY3
WHERE  ACCOUNT_ID BETWEEN 200000001 AND 300000000
UNION ALL
SELECT ACCOUNT_ID, PAYMENT_DATE, PAYMENT_AMOUNT,
       PAYMENT_TYPE, INVOICE_NUMBER
FROM   ACCOUNT_HISTORY4
WHERE  ACCOUNT_ID BETWEEN 300000001 AND 999999999;