Query Optimization in a Heterogeneous DBMS

Abstract

We propose a query optimization strategy for heterogeneous DBMSs that extends the traditional optimizer strategy widely used in commercial DBMSs to allow execution of queries over both known (i.e., proprietary) DBMSs and foreign vendor DBMSs that conform to some standard such as providing the usual relational database statistics. We assume that participating DBMSs are autonomous and may not be able, even if willing, to provide the cost model parameters. The novelty of the strategy is to deduce the necessary information by calibrating a given DBMS. As the calibration has to be done as a user, not as a system administrator, it poses unpredictability problems such as inferring the access methods used by the DBMS, idiosyncrasies of the storage subsystem and coincidental clustering of data. In this paper we propose a calibrating database which is synthetically created so as to make the process of deducing the cost model coefficients reasonably devoid of the unpredictability problems. Using this procedure, we calibrate three commercial DBMSs, namely Allbase, DB2, and Informix, and observe that in 80 % of the cases the estimate is quite accurate. 1 Mot ivat ion Heterogeneity of databases and database management systems (DBMSs) h ave gained its due recognition as a result of the advent of open systems. Typically this heterogeneity may result from semantic discrepancies in the data, multiple data models, different systems, etc. All of these are consequences of the need for independent database systems to interoperate while