Dynamic constraints derivation and maintenance in the teradata RDBMS

Abstract

We define a new algorithm that allows the Teradata query optimizer to automatically derive and maintain constraints between date type columns across tables that are related by referential integrity constraints. We provide a novel, quantitative measure of the usefulness of such rules. We show how the Teradata query optimizer utilizes these constraints in producing more optimal plans especially for databases that have tables that are value ordered by date. We also discuss our design for maintaining these constraints in the presence of inserts, deletes, and updates to the relevant tables. Finally, we give performance numbers for seven TPC-H queries from our prototype implementation based on the Teradata Database engine.