Re: tough choices

> > Of course it is supported by the optimizer. The use of multiple
partitions
> > enables parallel database access (inter-partition parallelism).
> > Intra-partition parallelism is available even on a single partition
system
> > in certain circumstances.
>
> So the optimizer knows that a query not requiring information from all
> but one partition can and will query only the single partition?
>

DB2 partitioning is designed to optimize parallel SQL operations and therefore data is randomly spread across the partitions with hash partitioning to spread the load across the partitions (each with its own processors, memory, etc.) This cuts down on the total execution time of the SQL in almost all cases. In the case of a single row retrieval based on the partitioning key, DB2 can calculate which partition has the data.

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. 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.

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

However, there is also a feature in DB2 called MDC (multi-dimensional clustering) which has some of the characteristics of range partitioning in that it groups like data close together in a partitioned environment.

We have been through this many times before.

The bottom line is that total performance of Oracle and DB2 are similar. Oracle "may" slightly excel in some areas and DB2 definitely excels in others. 99.9% of customers and applications would not notice much difference. The only exception would be if a company would use one database product for one type system, and a different database for another type application. The cost benefit tradeoff of that would be outweighed by the increased cost of not standardizing on a single product.

> >>>To the best of my knowledge DPF is offered on all supported platforms
of
> >>>DB2 for LUW.
> >>
> >>Not according to:
> >>http://www.developer.ibm.com/tech/faq/individual?oid=2:82779
> >>
> >
> > I don't know exactly what statement you are reading, but DPF
(partitioning)
> > is available on DB2 ESE for Linux, UNIX, and Windows. These are the
> > platforms known as LUW.
>
> Thanks. But still limited to ESE and still an additional license cost.
> Is that correct?
>

Yes DPF is an additional cost on top of the ESE license. Even so, it is less expensive than an equivalent Oracle system. Received on Sat Jun 19 2004 - 16:40:31 CDT