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.
[1]
Elizabeth O'Neil,et al.
Database--Principles, Programming, and Performance
,
1994
.
[2]
Jeffrey D. Ullman,et al.
Implementing data cubes efficiently
,
1996,
SIGMOD '96.
[3]
Goetz Graefe,et al.
Multi-table joins through bitmapped join indices
,
1995,
SGMD.
[4]
Patrick O'Neil.
Database systems: principles, programming, performance
,
1994
.
[5]
Patrick E. O'Neil.
The Set Query Benchmark
,
1991,
The Benchmark Handbook.
[6]
Douglas Comer,et al.
Ubiquitous B-Tree
,
1979,
CSUR.
[7]
Patrick E. O'Neil,et al.
Model 204 Architecture and Performance
,
1987,
HPTS.
[8]
Clark D. French,et al.
“One size fits all” database architectures do not work for DSS
,
1995,
SIGMOD '95.
[9]
David A. Patterson,et al.
Computer architecture (2nd ed.): a quantitative approach
,
1996
.
[10]
Jim Gray,et al.
The 5 minute rule for trading memory for disc accesses and the 10 byte rule for trading memory for CPU time
,
1987,
SIGMOD '87.
[11]
O'NeilPatrick,et al.
Improved query performance with variant indexes
,
1997
.
[12]
David A. Patterson,et al.
Computer Architecture: A Quantitative Approach
,
1969
.