Single table access using multiple indexes: Optimization, execution, and concurrency control techniques

  • C. Mohan
  • Don Haderle
  • Yun Wang
  • Josephine Cheng
Session 2: Data Structures
Part of the Lecture Notes in Computer Science book series (LNCS, volume 416)


Many data base management systems' query optimizers choose at most one index for accessing the records of a table in a given query, even though many indexes may exist on the table. In spite of the fact that there are some systems which use multiple indexes, very little has been published about the concurrency control or query optimization implications (e.g., deciding how many indexes to use) of using multiple indexes. This paper addresses these issues and presents solutions to the associated problems. Techniques are presented for the efficient handling of record ID lists, elimination of some locking, and determination of how many and which indexes to use. The techniques are adaptive in the sense that the execution strategies may be modified at run-time (e.g., not use some indexes which were to have been used), if the assumptions made at optimization-time (e.g., about selectivities) turn out to be wrong. Opportunities for exploiting parallelism are also identified. A subset of our ideas have been implemented in IBM's DB2 V2R2 relational data base management system.


Index Intersection Conjunctive Normal Form Query Optimization Index Union Execution Strategy 
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.

9. References

  1. Alur89.
    Alur, N. A Look at Version 2.2's Performance Enhancements, The Relational Journal for DB2 Users, Volume 1, Number 3, November 1989.Google Scholar
  2. BIEs76.
    Blasgen, M., Eswaran, K. On the Evaluation of Queries in a Relational Data Base System, IBM Research Report RJ1745, IBM San Jose Research Laboratory, April 1976.Google Scholar
  3. BIEs77.
    Blasgen, M., Eswaran, K. Storage and Access in Relational Databases, IBM Systems Journal, Vol. 16, No. 4, 1977.Google Scholar
  4. Bora88.
    Boral, H. Parallelism in Bubba, Proc. International Symposium on Databases for Parallel and Distributed Systems, Austin, December 1988.Google Scholar
  5. CLSW84.
    Cheng, J., Loosely, C., Shibamiya, A., Worthington, P. IBM Database 2 Performance: Design, Implementation, and Tuning, IBM Systems Journal, Vol. 23, No. 2, 1984.Google Scholar
  6. Daya87.
    Dayal, U. Of Nests and Trees: A Unified Approach to Processing Queries that Contain Nested Subqueries, Aggregates, and Quantifiers, Proc. 13th International Conference on Very Large Data Bases, Brighton, September 1987.Google Scholar
  7. EGLT76.
    Eswaran, K.P., Gray, J., Lorie, R., Traiger, I. The Notion of Consistency and Predicate Locks in a Database System, Communications of the ACM, Vol. 19, No. 11, November 1976.Google Scholar
  8. Gray78.
    Gray, J. Notes on Data Base Operating Systems, in Operating Systems — An Advanced Course, R. Bayer, R. Graham, and G. Seegmuller (Eds.), Lecture Notes in Computer Science, Volume 60, Springer-Verlag, 1978.Google Scholar
  9. LoYo89.
    Lorie, R., Young, H. A Low Communication Sort Algorithm for a Parallel Database Machine, Proc. 15th International Conference on Very Large Data Bases, Amsterdam, August 1989. Also Available as IBM Research Report RJ6669, IBM Almaden Research Center, February 1989.Google Scholar
  10. Lync88.
    Lynch, C. Selectivity Estimation and Query Optimization in Large Data Bases with Highly Skewed Distributions of Column Values, Proc. 14th International Conference on Very Large Data Bases, Los Angeles, August-September 1988.Google Scholar
  11. MHLPS89.
    Mohan, C., Haderle, D., Lindsay, B., Pirahesh, H., Schwarz, P. ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging, To Appear in ACM Transactions on Database Systems. Also Available as IBM Research Report RJ6649, IBM Almaden Research Center, January 1989.Google Scholar
  12. Moha89.
    Mohan, C. ARIESIKVL: A Key-Value Locking Method for Concurrency Control of Multiaction Transactions Operating on B-Tree Indexes, IBM Research Report RJ7008, IBM Almaden Research Center, September 1989.Google Scholar
  13. MoLe89.
    Mohan, C., Levine, F. ARIES/IM: An Efficient and High Concurrency Index Management Method Using Write-Ahead Logging, IBM Research Report RJ6846, IBM Almaden Research Center, August 1989.Google Scholar
  14. MuDe88.
    Muralikrishna, M., DeWitt, D. Equi-depth Multidimensional Histograms, Proc. ACM-SIGMOD International Conference on Management of Data, Chicago, June 1988.Google Scholar
  15. Mura88.
    Muralikrishna, M. Optimization of Multiple-Disjunct Queries in a Relational Database System, PhD Thesis, Computer Science Technical Report #750, University of Wisconsin at Madison, February 1988.Google Scholar
  16. Onei87.
    O'Neil, P. MODEL 204 Architecture and Performance, Proc. 2nd International Workshop on High Performance Transaction Systems, Asilomar, September 1987. Also in Lecture Notes in Computer Science Vol. 359, D. Gawlick, M. Haynie, A. Reuter (Eds.), Springer-Verlag, 1989.Google Scholar
  17. Onei89.
    O'Neil, P. A Set Query Benchmark for Large Databases, Proc. CMG Conference, Reno, December 1989.Google Scholar
  18. PMCLS89.
    Pirahesh, H., Mohan, C., Cheng, J., Liu, T.S., Selinger, P. Parallelism in Relational Data Base Systems: Architectural Issues and Design Approaches, IBM Research Report, IBM Almaden Research Center, December 1989.Google Scholar
  19. RoRe82.
    Rosenthal, A., Reiner, D. An Architecture for Query Optimization, Proc. ACM-SIGMOD International Conference on Management of Data, Orlando, June 1982.Google Scholar
  20. SACL79.
    Selinger, P., Astrahan, M., Chamberlin, D., Lorie, R., Price, T. Access Path Selection in a Relational Database Management System, Proc. ACM-SIGMOD International Conference on Management of Data, Boston, June 1979.Google Scholar
  21. TeGu84.
    Teng, J., Gumaer, R. Managing IBM Database 2 Buffers to Maximize Performance, IBM Systems Journal, Vol. 23, No. 2, 1984.Google Scholar
  22. ValH88.
    Varman, P., Iyer, B., Haderle, D. Parallel Merge on an Arbitrary Number of Processors, IBM Research Report RJ6632, IBM Almaden Research Center, December 1988.Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 1990

Authors and Affiliations

  • C. Mohan
    • 1
  • Don Haderle
    • 1
  • Yun Wang
    • 2
  • Josephine Cheng
    • 2
  1. 1.Data Base Technology InstituteIBM Almaden Research CenterSan JoseUSA
  2. 2.Data Base Technology InstituteIBM Santa Teresa LaboratorySan JoseUSA

Personalised recommendations