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.


Complex Object Aggregate Function Type Constructor Nest Operator Relational Dbms 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


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 Berlin Heidelberg 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