Abstract
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.
This is a preview of subscription content, log in via an institution to check access.
Preview
Unable to display preview. Download preview PDF.
9. References
Alur, N. A Look at Version 2.2's Performance Enhancements, The Relational Journal for DB2 Users, Volume 1, Number 3, November 1989.
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.
Blasgen, M., Eswaran, K. Storage and Access in Relational Databases, IBM Systems Journal, Vol. 16, No. 4, 1977.
Boral, H. Parallelism in Bubba, Proc. International Symposium on Databases for Parallel and Distributed Systems, Austin, December 1988.
Cheng, J., Loosely, C., Shibamiya, A., Worthington, P. IBM Database 2 Performance: Design, Implementation, and Tuning, IBM Systems Journal, Vol. 23, No. 2, 1984.
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.
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.
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.
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.
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.
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.
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.
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.
Muralikrishna, M., DeWitt, D. Equi-depth Multidimensional Histograms, Proc. ACM-SIGMOD International Conference on Management of Data, Chicago, June 1988.
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.
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.
O'Neil, P. A Set Query Benchmark for Large Databases, Proc. CMG Conference, Reno, December 1989.
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.
Rosenthal, A., Reiner, D. An Architecture for Query Optimization, Proc. ACM-SIGMOD International Conference on Management of Data, Orlando, June 1982.
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.
Teng, J., Gumaer, R. Managing IBM Database 2 Buffers to Maximize Performance, IBM Systems Journal, Vol. 23, No. 2, 1984.
Varman, P., Iyer, B., Haderle, D. Parallel Merge on an Arbitrary Number of Processors, IBM Research Report RJ6632, IBM Almaden Research Center, December 1988.
Author information
Authors and Affiliations
Editor information
Rights and permissions
Copyright information
© 1990 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Mohan, C., Haderle, D., Wang, Y., Cheng, J. (1990). Single table access using multiple indexes: Optimization, execution, and concurrency control techniques. In: Bancilhon, F., Thanos, C., Tsichritzis, D. (eds) Advances in Database Technology — EDBT '90. EDBT 1990. Lecture Notes in Computer Science, vol 416. Springer, Berlin, Heidelberg. https://doi.org/10.1007/BFb0022162
Download citation
DOI: https://doi.org/10.1007/BFb0022162
Published:
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-52291-1
Online ISBN: 978-3-540-46948-3
eBook Packages: Springer Book Archive