Re: tough choices

"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message news:fo5Bc.143108$Ly.137947_at_attbi_s01...
> > Range partitioning is not directly supported in DB2, but the same thing
can
> > be done with multiple tables and a UNION ALL view, and the optimizer
will
> > only query the table required.
>
> There is an implication here that Range Partitioning in Oracle and UNION
> ALL table views in DB2 are equivalent. They are not. UNION ALL views in
> DB2 are equivalent to UNIOn ALL views in Oracle. There are many
> additonal things you can do with Range Partitioning, including global
> indexes. That why Oracle implemented Range Partitioning (Oracle8) after
> it had implemented UNION ALL views (Oracle 7.1)
>

No they are not exactly the same. But they are close enough for most people and most applications. DB2 for z/OS has had range partitioning for over 15 years and it is not all the great in my opinion. Range partitioning certainly does not work well for share-nothing parallel operations. But since both DB2 for z/OS and Oracle only support share-everything parallelism, that is what they both have range partitioning instead of hash partitioning (hash partitioning is designed to split the load evenly across all partitions).
>
> > This has many benefits from a DBA standpoint
> > where partitions have historical data partitioned by date, because once
the
> > date has closed, the table can be backed up without continuously backing
up
> > a much larger partitioned table as new transactions are added to the
same
> > table in Oracle.
>
> There is an implication here that DB2's UNION ALL views better support
> rolling window operations than Oracle's Range Partitions. They are not.
> A major design point of Range Partitions when they were provided in
> Oracle8 was to add better support for rolling window operations than
> what could be done for UNION ALL tables. You can, of course, back up 1
> or as many partitions in a range partitioned table as you want to.
> That's sort of the point.
>

Having actually worked on such applications, I can say that it can be done quite well with DB2 UNION ALL views. If Oracle is slightly better in that respect with range partitioning, then fine. I don't think it is deal breaker. The use of range partitioning comes at a big cost, especially when trying to balance a load across multiple partitions for true parallel operations that are scalable. With range partitioning, one gets a lot of hot spots on a particular partition (which is usually the most current monthly or yearly data).
> >
> > The admin cost of adding new tables is about the same as adding new
> > partitions for each month (in an Oracle range partitioning scenario).
This
> > can be done manually or automatically via application software. One
example
> > of an application that uses multiple tables for time periods instead of
> > range partitioning is a check archive system by Viewpointe Archive
Services:
> > http://www.viewpointearchive.com/default.aspx?pageid=5
>
>
> Hmm - seems to require a LOT of help from IBM to make it work however -
>

In addition to the fact that the Viewpointe system check image system uses DB2 EEE version 7 with multiple partitions as their database, Viewpointe has also contracted with IBM to provide complete datacenter outsourcing services. This is not required to make DB2 work, but complete data center outsourcing is just another business that IBM happens to be involved in.

The good news is that a very large chuck of the check written in the US are stored on that system for many of the largest banks in the US, so some of your checks may be on there also. Maybe even some Oracle corporate checks. When adding over 80 million checks every business day to the database, fortunately they are using a robust and scalable database architecture. Received on Sat Jun 19 2004 - 22:49:48 CDT