Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                
skip to main content
article
Free access

Improved query performance with variant indexes

Published: 01 June 1997 Publication History

Abstract

The read-mostly environment of data warehousing makes it possible to use more complex indexes to speed up queries than in situations where concurrent updates are present. The current paper presents a short review of current indexing technology, including row-set representation by Bitmaps, and then introduces two approaches we call Bit-Sliced indexing and Projection indexing. A Projection index materializes all values of a column in RID order, and a Bit-Sliced index essentially takes an orthogonal bit-by-bit view of the same data. While some of these concepts started with the MODEL 204 product, and both Bit-Sliced and Projection indexing are now fully realized in Sybase IQ, this is the first rigorous examination of such indexing capabilities in the literature. We compare algorithms that become feasible with these variant index types against algorithms using more conventional indexes. The analysis demonstrates important performance advantages for variant indexes in some types of SQL aggregation, predicate evaluation, and grouping. The paper concludes by introducing a new method whereby multi-dimensional group-by queries, reminiscent of OLAP/Datacube queries but with more flexibility, can be very efficiently performed.

References

[1]
Comer, D. The Ubiquitous B-tree. Comput. Surv. 11 (1979), pp. 121-137.
[2]
Herb Edelstein. Faster Data Warehouses. Information Week, Dec. 4, 1995, pp. 77-88. Give title and author on http://www.techweb.com/search/advsearch.html.
[3]
Clark D. French. "One Size Fits All" Database Architectures Do Not Work for DSS. Proceedings of the 1995 ACM SIGMOD Conference, pp. 449-450.
[4]
Jim Gray, Adam Bosworth, Andrew Layman, and Hamid Pirahesh. Data Cube: A Relational Operator Generalizing Group-By, Cross-Tab, and Sub-Totals. Proc. 12th Int. Conf. on Data Eng., pp. 152-159, 1996.
[5]
Jim Gray and Franco Putzolu. The Five Minute Rule for Trading Memory for Disk Accesses and The 10 Byte Rule for Trading Memory for CPU Time. Proc. 1987 ACM SIGMOD, pp. 395-398.
[6]
Venky Harinarayan, Anand Rajaraman, and Jeffrey D. Ullman. Implementing Data Cubes Efficiently. Proc. 1996 ACM SIGMOD, pp. 205-216.
[7]
Ralph Kimball. The Data Warehouse Toolkit. John Wiley & Sons, 1996.
[8]
MODEL 204 File Manager's Guide, Version 2, Release 1.0, April 1989, Computer Corporation of America.
[9]
Patrick O'Neil. Model 204 Architecture and Performance. Springer-Verlag Lecture Notes in Computer Science 359, 2nd Int. Workshop on High Performance Transactions Systems (HPTS), Asilomar, CA, 1987, pp. 40-59.
[10]
Patrick O'Neil. The Set Query Benchmark. The Benchmark Handbook for Database and Transaction Processing Systems, Jim Gray (Ed.), Morgan Kaufmann, 2nd Ed. 1993, pp. 359-395.
[11]
Patrick O'Neil. Database: Principles, Programming, and Performance. Morgan Kaufmann, 3rd printing, 1996.
[12]
Patrick O'Neil and Goetz Graefe. Multi-Table Joins Through Bitmapped Join Indices. SIGMOD Record, September, 1995, pp. 8-11,
[13]
Patrick O'Neil and Dallan Quass. Improved Query Performance with Variant Indexes. Extended paper, available on h ttp :/www. c s. umb. edu/--po nei I/v ari nde xx. ps
[14]
D. A. Patterson and J. L. Hennessy. Computer Architecture, A Quantitative Approach. Morgan Kaufmann, 1996.
[15]
Stanford Technology Group, Inc., An INFORMIX Co. Designing the Data Warehouse on Relational Databases. lnformix White Paper, 1995, http://www.informix.com.
[16]
TPC Home Page. Descriptions and results of TPC benchmarks, including the TPC-C and TPC-D benchmarks. http://www.tpc.org.

Cited By

View all
  • (2022)Faster Multidimensional Data Queries on Infrastructure Monitoring SystemsBig Data Research10.1016/j.bdr.2021.10028827:COnline publication date: 28-Feb-2022
  • (2022)Hierarchical Locking in B-Tree IndexesOn Transactional Concurrency Control10.1007/978-3-031-01873-2_3(45-73)Online publication date: 26-Feb-2022
  • (2022)A Novel Approach of Using Materialized Queries for Retrieving Results from Data WarehouseProceedings of the International Conference on Intelligent Vision and Computing (ICIVC 2021)10.1007/978-3-030-97196-0_3(22-35)Online publication date: 24-Mar-2022
  • Show More Cited By

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM SIGMOD Record
ACM SIGMOD Record  Volume 26, Issue 2
June 1997
583 pages
ISSN:0163-5808
DOI:10.1145/253262
Issue’s Table of Contents
  • cover image ACM Conferences
    SIGMOD '97: Proceedings of the 1997 ACM SIGMOD international conference on Management of data
    June 1997
    594 pages
    ISBN:0897919114
    DOI:10.1145/253260
Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 01 June 1997
Published in SIGMOD Volume 26, Issue 2

Check for updates

Qualifiers

  • Article

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • Downloads (Last 12 months)328
  • Downloads (Last 6 weeks)50
Reflects downloads up to 12 Feb 2025

Other Metrics

Citations

Cited By

View all
  • (2022)Faster Multidimensional Data Queries on Infrastructure Monitoring SystemsBig Data Research10.1016/j.bdr.2021.10028827:COnline publication date: 28-Feb-2022
  • (2022)Hierarchical Locking in B-Tree IndexesOn Transactional Concurrency Control10.1007/978-3-031-01873-2_3(45-73)Online publication date: 26-Feb-2022
  • (2022)A Novel Approach of Using Materialized Queries for Retrieving Results from Data WarehouseProceedings of the International Conference on Intelligent Vision and Computing (ICIVC 2021)10.1007/978-3-030-97196-0_3(22-35)Online publication date: 24-Mar-2022
  • (2021)A Survey on Big IoT Data Indexing: Potential Solutions, Recent Advancements, and Open IssuesFuture Internet10.3390/fi1401001914:1(19)Online publication date: 31-Dec-2021
  • (2021)HyperspaceProceedings of the VLDB Endowment10.14778/3476311.347638214:12(3043-3055)Online publication date: 28-Oct-2021
  • (2020)Cuckoo indexProceedings of the VLDB Endowment10.14778/3424573.342457713:13(3559-3572)Online publication date: 27-Oct-2020
  • (2020)NeuroCardProceedings of the VLDB Endowment10.14778/3421424.342143214:1(61-73)Online publication date: 27-Oct-2020
  • (2020)Identifying insufficient data coverage in databases with multiple relationsProceedings of the VLDB Endowment10.14778/3407790.340782113:12(2229-2242)Online publication date: 14-Sep-2020
  • (2020)Big high-dimension data cube designs for hybrid memory systemsKnowledge and Information Systems10.1007/s10115-020-01505-962:12(4717-4746)Online publication date: 26-Aug-2020
  • (2020)DIFF: a relational interface for large-scale data explanationThe VLDB Journal10.1007/s00778-020-00633-6Online publication date: 30-Sep-2020
  • Show More Cited By

View Options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Login options

Figures

Tables

Media

Share

Share

Share this Publication link

Share on social media