P. ONeill and D. Quass. Improved Query Performance with Variant Indexes. Proc. of ACM SIGMOD Conf., Tucson, Arizona,1997, pp. 38-49.  Home/Search   Document Not in Database   Summary   Related Articles   Check

....OLAP data Since symmetrical clustering with respect to several dimensions is hard to achieve, most physical OLAP storage models either use non clustering indexes like secondary B Trees or cluster data with composite BTrees. The most prevalent OLAP data structure are bitmap indexes (e.g. [OQ97]) Bitmap indexes are useful, if multiple restrictions in low cardinality attributes like REGION or BRAND result in a very small selectivity (i.e. ratio of result set size and table size) for the conjunctive restriction. However, bitmap indexes are nonclustering secondary indexes which for small ....

P. ONeill and D. Quass. Improved Query Performance with Variant Indexes. Proc. of ACM SIGMOD Conf., Tucson, Arizona,1997, pp. 38-49.

....values, order preserving compression, Huffman encoding, Lempel Ziv, differencing, prefix and postfix compression, none of which support random access to tuples within a page. The above techniques, unlike ours, handle any kind of data, but introduce buffer and storage management problems. [15] discusses several query evaluation algorithms based on the use of compression. While this paper assumes gzip compression is used, our techniques could be used as well in most of the examples discussed there. 6 Conclusions and future work This paper presents a new compression algorithm and ....

P. O'Neil and D. Quass, "Improved Query Performance with Variant Indexes", in ACM SIGMOD 1997, pp. 38-49.

....to propose a new indexing technique encoded bitmap indexing. The main advantages of encoded bitmap indexes are a drastic reduction in space requirements and corresponding performance gains. 2. 1 Simple bitmap indexing revisited Simple bitmap indexing was first introduced in the Model 204 DBMS [9, 11]. The basic idea is to use a string of bits (0 or 1) to indicate whether an attribute in a table is equal to a specific value or not. The position of a bit in the bit string denotes the position of a tuple in the table. The bit is set, if the content of an attribute is associated with a specific ....

....total order preserving encoding is the internal representation of integers in computers, e.g. 8 is encoded as 1000 , 17 as 10001 . If we define the encoding as the internal representation of computers, the resulting encoded bitmap index is a set of bit slices of the original attribute. In [11], O Neil and Quass defined such an index as bit sliced index 12 and proposed algorithms for evaluating some query types directly from the bit sliced index. However, bit sliced index is not the only answer to numeric (or ordinal) attributes. For example, given an attribute A with domain f101; ....

[Article contains additional citation context not shown here]

P. O'Neil, D. Quass, Improved Query Performance with Variant Indexes, SIGMOD Conf., Tucson, Arizona, May 1997.

....as, precomputation of summarized data, predefined access paths, special index techniques, etc. In this paper, we propose encoded bitmap indexing an extension of known bitmap indexing, first proposed by O Neil in the Model 204 DBMS [9] In Section 2, we discuss bitmap indexing as proposed in [9, 11], and propose an encoded bitmap indexing to deal with large cardinality domains. We thus correct a shortcoming of simple bitmap indexing, which is best suited for low cardinality attributes. The basic idea of encoded bitmap indexing is to encode the attribute domain. Therefore, we also discuss how ....

....total order preserving encoding is the internal representation of integers in computers, e.g. 8 is encoded as 1000 , 17 as 10001 . If we define the encoding as the internal representation of computers, the resulting encoded bitmap index is a set of bit slices of the original attribute. In [11], O Neil and Quass defined such an index as bit sliced index and proposed algorithms for evaluating some query types directly from the bit sliced index. Mapping Table 101 000 102 001 103 010 104 100 105 101 106 110 Figure 6: Total order preserving encoding However, bit sliced index is not the ....

[Article contains additional citation context not shown here]

P. O'Neil, D. Quass, Improved Query Performance with Variant Indexes, SIGMOD Conf., Tucson, AZ, May 1997.

....The star join conditions typically include selection conditions on the dimension tables as well. The specialized nature of star schemas makes join indexes especially attractive for OLAP queries [17, 6] Different flavors of bitmap join indexes have been proposed to efficiently deal with star joins [17, 18]. In its simplest form [17] a bitmap join index is a bitmap index on the fact table T based on a single attribute A of a (star schema) dimension table V . The bitmap join index is useful precisely when the OLAP query specifies a selection condition on attribute A of dimension table V , and a ....

P. O'Neil and D. Quass. Improved query performance with variant indexes. In Proceedings of the ACM SIGMOD Conference on Management of Data, pages 38--49, 1997.

....B tree indexing scheme which is so apt for OLTP transactions is not the best suited to answer OLAP queries. We need to look at new indexing schemes and variants of existing schemes that will work well with OLAP queries. A number of indexing strategies have been suggested for data warehouses [2, 18, 40] in literature. We review a few important strategies. 4.1 Value List Index The value list index consists of two parts. The first part is a balanced tree structure and the second part is a mapping scheme. The mapping scheme is attached to the leaf nodes of the tree structure and points to the ....

....for each row r with ordinal number j that satisfies the predicate P, the jth bit in B is set. Bitmaps efficiently represent low cardinality data, however to make this indexing scheme practical for high cardinality data, compression techniques must be used. Value list indexes have been shown in [40] to outperform other access method in queries involving the MIN or MAX aggregate functions, as well as queries that compute percentile values of a given column. 4.2 Projection Index A projection index is equivalent to the column being indexed. If C is the column being indexed, then the ....

[Article contains additional citation context not shown here]

P. O'Neil and D. Quass. Improved query performance with variant indexes. In Proceedings ACM SIGMOD International Conference on Management of Data, Tucson, AZ, 1997.

....warehousing and in Section 3 we review various compression techniques. In Section 4 we discuss how compression may be applied to certain types of indexes and in Section 5 we conclude the paper. 2 Variant Indexes In this chapter we describe some of the variant indexes reported in previous work [PQ97], including the new DataIndexes [KA98] We also describe some of the algorithms to use with these indices. 2.1 Bitmap Indexes Most database systems today use B trees as indexes. This uses a tree ordering and has an entry for each key value which references all rows in the table having that key ....

....while most processors have single instructions to directly AND, OR and NOT machine words. Thus, Bitmap operations are a lot faster. When bitmaps are sparse one can compress them. The simplest of compression methods is to convert it back to a RID list. A further optimization described in [PQ97] is to divide the table into segments of a fixed number of rows each. The bitmaps corresponding to each segment is a fragment and each fragment can be stored as a bitmap or an RID list. This not only saves space, but also saves disk I O in combining bitmaps. This is because some fragments may not ....

Patrick O'Neil, Dallan Quass. Improved Query Performance with Variant Indexes. Proc. of SIGMOD, 1997.

....to access specific tuples) The technique of view materialization [9, 11, 21, 24, 28] has been proposed for reducing response times. This technique anticipates, and precomputes, the answers to frequently asked queries. It is recognized however, that such anticipation only works up to a point [19], and a bulk of the workload in DWMS will consist of ad hoc queries which will need to be computed on demand. There is virtually no work on strategies to reduce response times of this class of queries. This however, is a major impediment to the successful use of DWMS in decision support. This ....

....good support of range queries. However, conventional B trees support uni dimensional access to data (e.g. a B tree index constructed on an employee age attribute) and cannot be directly applied to multidimensional range queries. As a result, new access structures have been proposed for DWMS [18, 19]. Specifically, three indexes have been proposed: the value list index, the projection index and the bit sliced index. It has been shown that these indexes display better performance under decision support workloads than their conventional counterparts [19] These are already provided in ....

[Article contains additional citation context not shown here]

Patrick O'Neil and Dallan Quass. Improved query performance with variant indexes. to appear in Proceedings ACM SIGMOD International Conference on Management of Data ,Tucson, AZ, June 1997.

....reduction. The dynamic optimization discussed is the approach of inclusion and exclusion for both bit sliced indexes and encoded bitmap indexes. 1 Introduction Bitmap indexing has become a promising technique for query processing in DWs. Variations of bitmap indexes include bit sliced indexes [14, 3], encoded bitmap indexes (EBI) 18] bitmapped join indexes [13] range based bitmap indexes [20] and others[16] For query operations, such as selections, aggregates, and joins, query evaluation algorithms using bitmaps have been proposed in recent years. In this paper, we further explore the ....

....2 That is, combining multiple index structures to evaluate logical conjunction or disjunction of selection predicates. 2.1 Bit slicing and EBI revisited 2.1. 1 Bit slices A bit sliced index (named binary bit sliced index later) of an attribute is a bitwise projection of the attribute [14]. For example, suppose that the attribute A from Example 1 is defined as a two byte short integer. A binary bit sliced index on A consists of 16 bit vectors and is defined as shown in Figure 1 3 . Bits, b 0 Delta Delta Delta A Delta Delta Delta 201 100 900 b15 : b10 b9 b8 b7 b6 b5 ....

[Article contains additional citation context not shown here]

P. O'Neil, D. Quass, Improved Query Performance with Variant Indexes, SIGMOD, Tucson, Arizona, May 1997.

....optimal lattice path increases. 7 Related Work The data intensive nature of OLAP queries and their online response requirements has triggered substantial work on optimizing such queries. Previous approaches to this problem have been based on using materialized views [8, 10, 20, 1] indices [9, 16], and caching [19, 2] While all these approaches are important to improve the overall query response time of OLAP queries, they do not take into account the order in which cells are laid out on disk. Among these approaches, probably the work closest to ours is that of Deshpande et al. 2] They ....

P. O'Neil and D. Quass. Improved query performance with variant indexes. In Proceedings of the ACM SIGMOD Conference on Management of Data, pages 38--49, 1997.

....very large) More specifically, the SALES table will likely be indexed on each of its six dimensional attributes (namely, PartKey, SuppKey, CustKey, ShipDate, CommitDate and ReceiptDate) A number of indexing schemes have been proposed in the literature. Among these, four index types are shown in [13] to be particularly appropriate for OLAP systems: B trees , bitmapped indexes [12] projection indexes and bit sliced indexes [13] A DataIndex, like the projection index, exploits a positional indexing strategy. A projection index is simply a mirror image of the column being indexed. In ....

....CustKey, ShipDate, CommitDate and ReceiptDate) A number of indexing schemes have been proposed in the literature. Among these, four index types are shown in [13] to be particularly appropriate for OLAP systems: B trees , bitmapped indexes [12] projection indexes and bit sliced indexes [13]. A DataIndex, like the projection index, exploits a positional indexing strategy. A projection index is simply a mirror image of the column being indexed. In particular, when indexing columns of the fact table, both the index and the corresponding column in the Comment 4 bytes 4 bytes 4 bytes 8 ....

[Article contains additional citation context not shown here]

P. O'Neil and D. Quass. Improved query performance with variant indexes. In J. M. Peckman, editor, Proc. ACM SIGMOD, volume 26(2) of SIGMOD Record, pages 38--49, Tucson, Arizona, May 13-15 1997.

....outer references are p partkey in Query 1 and ps.ps partkey in Query 2. Each column has a fast projection index, which is a compressed list of column values in TID order. Every key column has a high nongroup index, which consists of a traditional B tree and a Bit Sliced index described in [OQ97] There is also a high non group index on ps availqty. Other columns in the local selection predicates have low fast indexes on them. A low fast index is basically a Bitmap index ( OQ97] Our first experiment was done on Query 1(a) which is derived by changing the nested predicate in Query 1 to ....

....has a high nongroup index, which consists of a traditional B tree and a Bit Sliced index described in [OQ97] There is also a high non group index on ps availqty. Other columns in the local selection predicates have low fast indexes on them. A low fast index is basically a Bitmap index ( OQ97] Our first experiment was done on Query 1(a) which is derived by changing the nested predicate in Query 1 to ps supplycost not in SUBQUERY and omitting the MIN in the SELECT clause of the subquery. This is a non rewritable query. There is a four way join in the correlated subquery. NL ....

Patrick O'Neil and Dallan Quas. Improved query performance with variant indexes. In Proceedings of the ACM SIGMOD Conference, pages 38--49, 1997.

....[7] It is often used by management analysts and decision makers in a variety of functional areas such as sales and marketing planning. Typically, OLAP queries look for specific trends and anomalies in the base information by aggregating, ranging, filtering and grouping data in many different ways [22]. Efficient query processing is a critical requirement for OLAP because the underlying data warehouse is very large, queries are often quite complex, and decision support applications typically require interactive response times. Data warehousing OLAP systems are best understood by comparing them ....

....database (MDDB) approach. There are currently numerous commercial products available based on the MDDB approach, including Hyperion s Essbase [15] Oracle s Express [23] and MicroStrategy s DSS Agent [20] to name a few. It is recognized however, that such pre aggregation only works up to a point [22], and precomputing a data cube is often infeasible, as the number of tables to form the cube grows exponentially with the number of dimensions [14] Moreover, a considerable fraction of the workload in OLAP applications will consist of ad hoc queries which will need to be computed on demand [2] ....

[Article contains additional citation context not shown here]

P. O'Neil and D. Quass. Improved query performance with variant indexes. In Proc. ACM SIGMOD Intl. Conf. on Management of Data, pages 38--49, Tucson, AZ, May 13-15 1997.

....the optimal design of bitmaps, and algorithms based on tree and logical reduction. The dynamic optimization issue includes the approach of inclusion exclusion for both bit sliced indexes and encoded bitmap indexes. 1 Introduction Bitmap indexing has sparked recent discussion in the area of DWing [13, 19, 14, 16, 18, 3]. Typically, queries in DWs are read only, access large portion of the underlying tables, and have complex selection predicates involving arbitrary group bys and aggregation functions. In addition, instead of transaction throughput, response time is the criterion of the performance evaluation in ....

....for query processing in DWing and has advantages over traditional indexing, such as B trees, since B trees are designed to support frequently update operations, where the update operations affect only a tiny portion of the underlying tables. Variations of bitmap indexes include bit sliced indexes [14, 3], encoded bitmap indexes (EBI) 18] bitmapped join indexes [13] range based bitmap indexes [19] etc. For query operations, such as selections, aggregates and joins, algorithms using bitmap indexes have been proposed in last years. In this paper, we further explore the issues of query ....

[Article contains additional citation context not shown here]

P. O'Neil, D. Quass, Improved Query Performance with Variant Indexes, SIGMOD Conf., Tucson, Arizona, May 1997.

....the answers to all possible queries. Thus, ad hoc querying must be supported in data warehouses. This realization has sparked an interest in exploring indexing strategies suitable for OLAP queries. There appears to have been relatively little work done in ad hoc query support for data warehouses [45, 46, 55, 39]. In this paper we propose DataIndexes as a new paradigm for storing the base data. An attractive feature of DataIndexes is that they serve as indexes as well as the store of base data. Thus, DataIndexes actually define a physical design strategy for a data warehouse where the indexing, for all ....

....14, 22, 24, 25, 26, 28, 40, 41, 48, 49, 70] The basic premise underlying this work is that data warehouses can achieve faster response times by pre aggregating (i.e. materializing) the answers to frequently asked queries. It is recognized however, that such anticipation only works up to a point [11, 46], and a considerable fraction of the workload in OLAP applications will consist of ad hoc queries which will need to be computed on demand [3] This has led to work on strategies for ad hoc query processing. 2. Ad hoc Strategies. This approach to fast OLAP query processing supports ad hoc querying ....

[Article contains additional citation context not shown here]

P. O'Neil and D. Quass. Improved query performance with variant indexes. In Proc. ACM SIGMOD Intl. Conf. on Management of Data, pages 38--49, Tucson, AZ, May 13-15 1997.

....queries and multi dimensional queries are common. In many of these application, queries may join multiple tables (for example, a fact table may be joined with many dimension tables) These query characteristics require the development of new indexing mechanisms for enhancing query performance [27, 40]. To support complex queries over multiple tables, multi table indices have been developed including join indices and star indices. These indices materialize (i.e. cache) common joins enabling complex queries to be performed efficiently. Traditional indices have been generalized to enable the ....

P. O'Neill and D. Quass. "Improved Query Performance with Variant Indexes". In Proceedings of the 1997 ACM SIGMOD International Conference on Management of Data, Tuscon, Arizona, June 1997.

....queries over star or snowflake schemas. These techniques can indeed greatly improve relational performance for multi dimensional queries when compared with more traditional alternatives such as pipelined left deep hash joins for consolidation queries [Su96] or B tree indexes for star select joins [OQ97]. However, the recent emergence of object relational database systems [Ki95, St96] has opened the door to another potential approach to solving the OLAP query performance problem: since object relational database systems can be extended to support the storage and manipulation of multi dimensional ....

....that it dominated the other techniques over the full range of queries tested in this paper. For details of these tests please see [RQZN] Bitmap Indexes are becoming widely used in relational systems [SybaseIQ, Redbrick, Informix] to speed up the evaluation of consolidation queries with selection [OQ97]. We have implemented bitmap indices in Paradise. We have also implemented a specialized file structure optimized for tables with small, fixed size records. We call this structure a fact file for obvious reasons. Our main reason for implementing this file is to do everything possible to ensure ....

Patrick O'Neil and Dallan Quass. "Improved Query Performance with Variant Indexes." Proc. of the 1997 SIGMOD Conference, May, 1997.

....we conclude the paper. 2 A Physical Design Principle to Exploit Parallelism In this section we show how by judiciously using many of the indexing schemes proposed in the literature, we can structure a data warehouse to make it amenable to parallel query processing. Four index types are shown in [16] to be particularly appropriate for OLAP systems: B trees , indexes based on bitmaps [16, 15] projection indexes and bit sliced indexes [16] Consider the division of the SALES table in Figure 1A, into seven smaller tables, as shown in Figure 1B. This scheme is composed of 7 vertical ....

....we show how by judiciously using many of the indexing schemes proposed in the literature, we can structure a data warehouse to make it amenable to parallel query processing. Four index types are shown in [16] to be particularly appropriate for OLAP systems: B trees , indexes based on bitmaps [16, 15], projection indexes and bit sliced indexes [16] Consider the division of the SALES table in Figure 1A, into seven smaller tables, as shown in Figure 1B. This scheme is composed of 7 vertical partitions: one for each of the dimensional attributes and one for the remaining columns from the ....

[Article contains additional citation context not shown here]

P. O'Neil and D. Quass. Improved query performance with variant indexes. In Proc. ACM SIGMOD Intl. Conf. on Management of Data, pages 38--49, Tucson, AZ, May 13-15 1997.

....B f , one loops from the largest value in the Value List index down to the smallest, until finding a row in B f . To find MAX and MIN using a Projection index, one must loop through all values stored. The algorithm to evaluate MAX or MIN using a Bit Sliced index is given in our extended paper, [O NQUA], together with other algorithms not detailed in this Section. 2 Best only if there is a clustering of rows in B in a local region, a fraction f of the pages, f 0.755. To calculate MEDIAN(C) with C a keyvalue in a Value List index, one loops through the non null values of C in decreasing (or ....

....indexes are not useful for evaluating MEDIAN, unless the number of rows in the Foundset is very small, since all values have to be extracted and sorted. Surprisingly, a Bit Sliced index can also be used to determine the MEDIAN, in about the same amount of time as it takes to determine SUM (see [O NQUA]) The N TILE aggregate function finds values v 1 , v 2 , v N 1 , which partition the rows in B f into N sets of (approximately) equal size based on the interval in which their C value falls: C = v 1 , v 1 C = v 2 , v N 1 C. MEDIAN equals 2 TILE. An example of a COLUMN PRODUCT ....

[Article contains additional citation context not shown here]

Patrick O'Neil and Dallan Quass. Improved Query Performance with Variant Indexes. Extended paper, available on http:/www.cs.umb.edu/~poneil/varindexx.ps

No context found.

P. O'Neil and D. Quass. Improved Query Performance with Variant Indexes. In Proceedings of the Intl. ACM SIGMOD Conference, pages 38--49, Tucson, Arizona, May 1997.

No context found.

P. O'Neil, D. Quass, Improved Query Performance with Variant Indexes. Proc. ACM SIGMOD Int. Conf. on Management of Data, 38--49, 1997.

Online articles have much greater impact   More about CiteSeer.IST   Add search form to your site   Submit documents   Feedback  

CiteSeer.IST - Copyright Penn State and NEC