In this paper, we describe an implementation to two SQO techniques, Predicate Introduction and Join Elimination, in DB2 Universal Database. We present the implemented algorithms and performance results using the TPCS and APB-1 OLAP benchmarks. Our experiments show that SQO can lead to dramatic query performance improvements. A crucial aspect of our implementation of SQO is the fact that it does not rely on complex integrity constraints (as many previous SQO techniques did); we use only referential integrity constraints and check constraints.

[1]

Upen S. Chakravarthy , John Grant , Jack Minker : Logic-Based Approach to Semantic Query Optimization. TODS 15(2) : 162-207(1990)

[2]

Surajit Chaudhuri , Kyuseok Shim : Including Group-By in Query Optimization. VLDB 1994 : 354-366

[3]

...

[4]

...

[5]

...

[6]

Laura M. Haas , Walter Chang , Guy M. Lohman , John McPherson , Paul F. Wilms , George Lapis , Bruce G. Lindsay , Hamid Pirahesh , Michael J. Carey , Eugene J. Shekita : Starburst Mid-Flight: As the Dust Clears. TKDE 2(1) : 143-160(1990)

[7]

Michael Hammer , Stanley B. Zdonik : Knowledge-Based Query Processing. VLDB 1980 : 137-147

[8]

Matthias Jarke , James Clifford , Yannis Vassiliou : An Optimizing Prolog Front-End to a Relational Query System. SIGMOD Conference 1984 : 296-306

[9]

Jonathan J. King : QUIST: A System for Semantic Query Optimization in Relational Databases. VLDB 1981 : 510-517

[10]

Alon Y. Levy , Inderpal Singh Mumick , Yehoshua Sagiv : Query Optimization by Predicate Move-Around. VLDB 1994 : 96-107

[11]

Inderpal Singh Mumick , Hamid Pirahesh : Implementation of Magic-sets in a Relational Database System. SIGMOD Conference 1994 : 103-114

[12]

G. N. Paulley , Per-Åke Larson : Exploiting Uniqueness in Query Optimization. ICDE 1994 : 68-79

[13]

Hamid Pirahesh , Joseph M. Hellerstein , Waqar Hasan : Extensible/Rule Based Query Rewrite Optimization in Starburst. SIGMOD Conference 1992 : 39-48

[14]

Hamid Pirahesh , T. Y. Cliff Leung , Waqar Hasan : A Rule Engine for Query Transformation in Starburst and IBM DB2 C/S DBMS. ICDE 1997 : 391-400

[15]

Praveen Seshadri , Joseph M. Hellerstein , Hamid Pirahesh , T. Y. Cliff Leung , Raghu Ramakrishnan , Divesh Srivastava , Peter J. Stuckey , S. Sudarshan : Cost-Based Optimization for Magic: Algebra and Implementation. SIGMOD Conf. 1996 : 435-446

[16]

Shashi Shekhar , Jaideep Srivastava , Soumitra Dutta : A Formal Model of Trade-off between Optimization and Execution Costs in Semantic Query Optimization. VLDB 1988 : 457-467

[17]

Sreekumar T. Shenoy , Z. Meral Özsoyoglu : Design and Implementation of a Semantic Query Optimizer. TKDE 1(3) : 344-361(1989)

[18]

David E. Simmen , Eugene J. Shekita , Timothy Malkemus : Fundamental Techniques for Order Optimization. SIGMOD Conf. 1996 : 57-67

[19]

Transaction Processing Performance Council. http://www.tpc.org

Referenced by

1. Richard T. Snodgrass : Review - Implementation of Two Semantic Query Optimization Techniques in DB2 Universal Database. ACM SIGMOD Digital Review 1 : (1999)