Re: tough choices

From: Serge Rielau <srielau_at_ca.eye-be-em.com>
Date: Thu, 24 Jun 2004 01:12:41 -0400
Message-ID: <cbdnss$4gg$1@hanover.torolab.ibm.com>

Mark Townsend wrote:

> Serge Rielau wrote:
>

>> In a "partitioned" view case, which is the one we are debating here,
>> The compiler will find that the same indices are present for all tables.
>> The SELECT FROM V WHERE <input> with V = UNION ALL (T1, ..., Tn) will 
>> be rewritten into a SELECT FROM T(i) with i = foo(<input>) and 
>> associated indexes Ix(i)

> For secondary indexes as well ? Can uniqueness be enforced across the
> tables for things other than the primary key ?
As long as the partitioning columns are part of the unique key. General non unique indexes are no problem of course.

 > What happens if a constraint is not in place at the time of the DML ? Sorry to be blunt: But that is a stupid comment if I ever heard one. Each partition in a range partitioned table obeys the range, dohhh! If that's no comnstraint what is?
For a UNION ALL view the partitioning is described in constraints. For partitioned tables in the table DDL. I wasn't even drawing a comparison between DB2 UNION ALL vs. Oracle range partitioning. I'm simply describing what DB2 UNION ALL views do and that it's more than one would ordinarily expect and that they possess many aspects of what one would expect only from table tange partitioning.
If you feel the need for a p***ing contest do it alone.

>> Statistics are also computed with some magic.

> Including overall skew at the table or index level ? Will one or more
> individual execution plans be merged into an overall range scan and
> executed in parallel if it makes sense ? Does the possible degree of
> parallelism only match the tables left in the query after the elimination.
So, Mark, just how deep is your Oracle optimizer knowledge. My DB2 Optimizer knowledge is fair at best despite latching right onto it every day. Do you really think we should go into comparing DB2 SMP capabilities against Oracle SMP capabilities? It would be a good piece of work to just describe the basic lingo such as what straw scans are. And then of cours ethe magic question: Does one want SMP and when? And then who will decide what is better, given the tight integration into the overal design? It's like one of those embararsing pseudo scientific matches on Discover Channel:
"The Big White Shark vs the Alligator" *drumroll*

I've got a product to deliver. I take time to teach, I won't waste my time on: "But my daddy drives...."

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Received on Thu Jun 24 2004 - 00:12:41 CDT