Granular Indices for HQL Analytic Queries

Abstract

Database management systems use numerous optimization techniques to accelerate complex analytical queries. Such queries have to scan enormous amounts of records. The usual technique to reduce their run-time is the materialization of partial aggregates of base data. In previous papers we have proposed the concept of metagranules, i.e. partially ordered aggregations of the fact table. When a query is posed, the actual aggregation level will be determined and the smallest fit metagranule (materialized aggregation) will be used instead of the fact table. In this paper we extend that idea with metagranular indices, i.e. indices on metagranules. Assume a user issuing an aggregate query to a fact table with a selective HAVING or small LIMIT-ORDER BY clause. The database engine can not only identify the best metagranule but it can also use the index on that metagranule in order not to scan its full content. In this paper we present the proposed optimization method based on metagranular indices. We also describe its proof-of-concept prototype implementation. Finally, we report the results of performance experiments on database instances up to 350GiB. © Springer International Publishing Switzerland 2014.