Re: Distributed Join

>I remain very sceptical. To optimize a join across multiple vendor databases >requires that your 'joining' product must fully familiar with all >the individual optimizers in each database to understand how they will process >their portion of the query. Next it must be familiar with each vendors >'statistics' and translate those into a generic set for use in it's own >optimization. (This also means significant maintenance for the product since >evryone's statistics and optimization are proprietary and there are no >associated standards) Finally it must have a place to store >relative communications costs to each node and then consider that during >optimization. Fact is most of the vendors are not doing all this within their >own databases yet never mind a multi-vendor join. >It's realtively simple to do a distributed join, quite another thing to >optimize it! >Lawrence....... Find out more about DBI and you'll be pleasantly surprised to learn how much it does towards heterogenous, distributed join optimization. DBI has a cost-based optimizer that takes into account network cost (currently measured dynamically after attaching to the underlying database). DBI also imports into its global catalog cardinality information for underlying database tables. Underlying table index information is also imported. DBI's optimizer also takes into account individual databases' capabilities so as to determine how much of the query can be sent down as opposed to performing the operations in DBI. This capability information is fine-grained, meaning that it even captures capabilities such as "are subqueries supported in context of an insert statement by underlying database system". DBI's optimizer also does various query transformations such as predicate transitivity, view integration, partition elimination, select block merging, independent subquery pulling, pushing down limit-to/order-by/where clauses, unnesting, etc DBI also supports parallel query execution. All this and more in just the 2nd release of the product. If you need more info. pertaining to DBI send me mail at vishu@broke.enet.dec.com. - vishu (DB Integrator Development, Digital Equipment Corp.)