Noons wrote:
> Serge Rielau apparently said,on my timestamp of 23/06/2004 10:39 PM:
>> DB2 UDB for LUW even collapses a UNION ALL view into a single
>> "parameterized table" if possible, it pushes UPDATE and DELETE through
>> UNION ALL. At this point UNION ALL and range-partitioning effectively
>> move very close together in those DML respects.
> OK, so how does the optimizer handle indexes in this UNION ALL of
> many tables?
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)
Statistics are also computed with some magic.
Essentially what you see is a partitioned table. It just has different
lingo attached to it. But for all the optimizer cares it is.
This makes the difference between a partitioned table and a partitioned
view the administration which's importance differs from case to case.
>> Nonetheless I see having sophisticated UNION ALL technology as
>> important in the broader context, especially in the context of
>> federated databases.
> Again: so how do you optimize a UNION ALL across federated databases?
sing information constarints/where predicates it is easy to do branch
elimination with a therem prover or at least prove the partitioning
property. The result is that only relevant remote tables will be queried
which, in a federated environment, is a big deal.
>> the rides at your favorite theme park are also all free. Pitty if you
>> don't use them all after paying the entrance fee.
> That is how parks make moolah... :)
Indeed.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Received on Wed Jun 23 2004 - 13:00:30 CDT
