UDB Range Partitioning in V8: Using Ranges in Constraints

[Nate Eaton]

According to the original whitepaper on UDB range partitioning (http://
www-106.ibm.com/developerworks/db2/library/techarticle/0202zuzarte/
0202zuzarte.pdf), you can use a range as a criteria, either in the
UNION ALL view or in constraints. One restriction it lists, though, is
that the optimizer can't use a constraint that references a range
versus a discrete value for SQL containing host variables or parameter
markers. That was for V7 and the document indicated this would change
in the future. It is now supported in V9 with the native range
partitioning capabilities of the CREATE TABLE statement. What about
V8? I'm working on an effort that would greatly benefit from range
partitioning (history table containing over 5B rows) but the natural
value to use for range partitioning is a date that is continuously
valued. We could add a discreet date (i.e., 1st of the month for each
month/year associated with one of the event dates) but unless users
always explicitely reference the 1st of the month date, we won't see
branch elimination. We could add the ranges to the UNION ALL view but
would prefer not invalidating all the other views in the system that
would be dependent on it.

Regards,
Nathan