Re: Distributed Join

>That covers joins. What about aggregation? > >For instance, suppose I have two database instances, one with a table >called stock_uk and one with a table stock_us. Then on the US instance I >define a view stock_corp as 'SELECT * from stock_uk UNION SELECT * from >stock_us'. > >If I then issue the query 'SELECT sum(pieces) from stock_corp', my >understanding is that in most implementations, the US database will go >and get all rows from stock_uk and then perform the sum. What I'd like >would be for the database to execute it as >SUM(SUM(stock_uk.pieces)+SUM(stock_us.pieces)). DBI engineering has done some investigation into decomposing aggregates in the context of our horizontally partitioned views (HPVs). We are looking at putting at least a subset of this into the next release of DBI. Example: create view stock_corp (pieces, country) usinghorizontal partitioning on country partition p1 where country = 'UK' compose as select pieces, country from stock_uk partition p2 where country = 'US' compose as select pieces, country from stock_us; select sum(pieces) from stock_corp; The sum will get "pushed" into the partitions of the HPV. So it will be executed as SUM(SUM(stock_uk.pieces)+SUM(stock_us.pieces)). Combined with the compile-time partition elimination, runtime partition elimination, and parallel execution that DBI can do with HPVs, this should have an even greater impact. Of course as you mentioned, this gets more complicated for aggregates like AVG. Therefore, we may stick with SUM, MAX and MIN with the first implementation and then incrementally add others. Cheryl Thompson (DB Integrator Development, Digital Equipment Corp.)