Exploiting constraint-like data characterizations in query optimization Parke Godfrey, Jarek Gryz, and Calisto Zuzarte   View Paper (PDF)   Return to Industrial Sessions Abstract Query optimizers nowadays draw upon many sources of in­ formation about the database to optimize queries. They employ runtime statistics in cost­based estimation of query plans. They employ integrity constraints in the query rewrite process. Primary and foreign key constraints have long played a role in the optimizer, both for rewrite opportuni­ ties and for providing more accurate cost predictions. More recently, other types of integrity constraints are being ex­ ploited by optimizers in commercial systems, for which cer­ tain semantic query optimization techniques have now been implemented. These new optimization strategies that exploit constraints hold the promise for good improvement. Their weakness, however, is that often the ``constraints'' that would be use­ ful for optimization for a given database and workload are not explicitly available for the optimizer. Data mining tools can find such ``constraints'' that are true of the database, but then there is the question of how this information can be kept by the database system, and how to make this infor­ mation available to, and effectively usable by, the optimizer. We present our work on soft constraints in DB2. A soft con­ straint is a syntactic statement equivalent to an integrity constraint declaration. A soft constraint is not really a constraint, per se, since future updates may undermine it. While a soft constraint is valid, however, it can be used by the optimizer in the same way integrity constraints are. We present two forms of soft constraint: absolute and statistical. An absolute soft constraint is consistent with respect to the current state of the database, just in the same way an in­ tegrity constraint must be. They can be used in rewrite, as well as in cost estimation. A statistical soft constraint differs in that it may have some degree of violation with respect to the state of the database. Thus, statistical soft constraints cannot be used in rewrite, but they can still be used in cost estimation. We are working long­term on absolute soft constraints. We discuss the issues involved in implementing a facility for ab­ solute soft constraints in a database system (and in DB2), and the strategies that we are researching. The current DB2 optimizer is more amenable to adding facilities for statistical soft constraints. In the short­term, we have been implement­ ing pathways in the optimizer for statistical soft constraints. We discuss this implementation. DiSC'02 © 2003 Association for Computing Machinery