Optimization of nested queries in a complex object model

  • Hennie J. Steenhagen
  • Peter M. G. Apers
  • Henk M. Blanken
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 779)


Transformation of nested SQL queries into join queries is advantageous because a nested SQL query can be looked upon as a nested-loop join, which is just one of the several join implementations that may be available in a relational DBMS. In join queries, dangling (unmatched) operand tuples are lost, which causes a problem in transforming nested queries having the aggregate function COUNT between query blocks-a problem that has become well-known as the COUNT bug. In the relational context, the outerjoin has been employed to solve the COUNT bug. In complex object models supporting an SQL-like query language, transformation of nested queries into join queries is an important optimization issue as well. The COUNT bug turns out to be a special case of a general problem being revealed in a complex object model. To solve the more general problem, we introduce the nest join operator, which is a generalization of the outerjoin for complex objects.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Bal, R. et al.: The TM Manual version 2.0. University of Twente, Enschede (1993)Google Scholar
  2. 2.
    Balsters, H., de By, R.A., and Zicari, R.: Typed Sets as a Basis for Object-Oriented Database Schemas. Proceedings ECOOP, Kaiserslautern (1993)Google Scholar
  3. 3.
    Balsters, H. and de Vreeze, C.C.: A Semantics of Object-Oriented Sets. Proceedings 3rd International Workshop on Database Programming Languages, Nafplion, Greece (1991)Google Scholar
  4. 4.
    Dayal, U.: Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers. Proceedings VLDB, Brighton (1987)Google Scholar
  5. 5.
    Ganski, R.A. and Wong, A.K.T.: Optimization of Nested SQL Queries Revisited. Proceedings SIGMOD (1987)Google Scholar
  6. 6.
    Kiesling, W.: SQL-like and QUEL-like Correlation Queries with Aggregates Revisited. Memorandum UCB/ERL 84/75, Berkeley (1984)Google Scholar
  7. 7.
    Kim, W.: On Optimizing an SQL-like Nested Query. ACM Transactions on Database Systems 7 3 (1982) 443–469Google Scholar
  8. 8.
    Muralikrishna, M.: Optimization and Dataflow Algorithms for Nested Tree Queries. Proceedings VLDB, Amsterdam (1989)Google Scholar
  9. 9.
    Muralikrishna, M.: Improved Unnesting Algorithms for Join Aggregate SQL Queries. Proceedings VLDB. Vancouver, 1992.Google Scholar
  10. 10.
    Pistor, P. and Andersen, F.: Designing a Generalized NF2 Model with an SQL-type Language Interface. Proceedings VLDB, Kyoto (1986)Google Scholar
  11. 11.
    Rosenthal, A. and Galindo-Legaria, C.: Query Graphs, Implementing Trees, and Freely-Reorderable Outerjoins. Proceedings SIGMOD, Atlantic City (1990)Google Scholar
  12. 12.
    Schek, H.J. and Scholl, M.H.: The Relational Model with Relation-Valued Attributes. Information Systems 11 2 (1986) 137–147Google Scholar
  13. 13.
    Scholl, M.H.: Theoretical Foundations of Algebraic Optimization Utilizing Unnormalized Relations. Proceedings First International Conference on Database Theory. LNCS 243, Springer-Verlag (1986)Google Scholar

Copyright information

© Springer-Verlag 1994

Authors and Affiliations

  • Hennie J. Steenhagen
    • 1
  • Peter M. G. Apers
    • 1
  • Henk M. Blanken
    • 1
  1. 1.Department of Computer ScienceUniversity of TwenteAE EnschedeThe Netherlands

Personalised recommendations