Journal of Intelligent Information Systems

, Volume 34, Issue 3, pp 345–366 | Cite as

Queries with CASE expressions

  • Jarek GryzEmail author
  • Qiong Wang
  • Xiaoyan Qian
  • Calisto Zuzarte


In recent years more and more queries are generated automatically by query managers/builders with end-users providing only specific parameters through GUIs. Queries generated automatically can be quite different from queries written by humans. In particular, they contain non-declarative features, most notorious of which is the CASE expression. Current query optimizers are often ill-prepared for the new types of queries as they do not deal well with procedural ‘insertions’. In this paper, we discuss the inefficiencies of CASE expressions and present several new optimization techniques to address them. We also describe experimental evaluation of the prototype implemented in DB2 UDB V8.2.


Database systems Query optimization 



TPCH is a trademark of Transaction Processing Council.


  1. Burns, D. (2006). Improving sql efficiency using case.
  2. Dalvi, N. N., Sanghai, S. K., Roy, P., & Sudarshan, S. (2001). Pipelining in multi-query optimization. In Proceedings of PODS (pp. 59–70).Google Scholar
  3. Haas, L. M., Freytag, J. C., Lohman, G. M., & Pirahesh, H. (1989). Extensible query processing in starburst. In SIGMOD proceedings (pp. 377–388). New York: ACM.Google Scholar
  4. Henderyckx, J. (1998). Version 5 case expressions: Beyond sql reference. The IDUG Solutions Journal, 5(3), 38–45.Google Scholar
  5. Lehner, W., Cochrane, B., Pirahesh, H., & Zaharioudakis, M. (2001). Fast refresh using mass query optimization. In Proceedings of ICDE (pP. 391–400).Google Scholar
  6. Pirahesh, H., Leung, T. Y. C., & Hasan, W. (1997). A rule engine for query transformation in Starburst and IBM DB2 C/S DBMS. In Proc. ICDE (pp. 391–400).Google Scholar

Copyright information

© Springer Science+Business Media, LLC 2009

Authors and Affiliations

  • Jarek Gryz
    • 1
    Email author
  • Qiong Wang
    • 2
  • Xiaoyan Qian
    • 2
  • Calisto Zuzarte
    • 2
  1. 1.Department of Computer Science and EngineeringYork UniversityTorontoCanada
  2. 2.IBM LaboratoryTorontoCanada

Personalised recommendations