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

Advantages of UNION in a View

By separating the data into different tables and creating the view over the tables, you can create a logical account history table with these distinct advantages over a single physical table:

• You can add or remove tables with small outages, usually just the time it takes to drop and recreate the view.
• You can partition each of the underlying tables, creating still smaller physical database objects.
• NPSIs on each of the underlying tables can be much smaller and easier to manage than they would be under a single table design.
• Utility operations can execute against an individual underlying table, or just a partition of that underlying table. This practice greatly decreases utility times against these individual pieces, and improves concurrency, as well as providing full partition independence.
• The view can be referenced in any SELECT statement in the same way as a physical table would be.
• Each underlying table could be as large as 16 TB, logically setting the size limit of the table that is represented by the view at 64 TB.
• Each underlying table could be clustered differently, or could be a segmented or partitioned tablespace.

DB2 distributes predicates against the view to every query block within the view and then compares the predicates. Any impossible predicates will result in the query block being pruned (not executed).