On the selection of secondary indices in relational databases

Abstract

An important problem in the physical design of databases is the selection of secondary indices. In general, this problem cannot be solved in an optimal way due to the complexity of the selection process. Often use is made of heuristics such as the well-known ADD and DROP algorithms. In this paper it will be shown that frequently used cost functions can be classified as super-or submodular functions. For these functions several mathematical properties have been derived which reduce the complexity of the index selection problem. These properties will be used to develop a tool for physical database design and also give a mathematical foundation for the success of the before-mentioned ADD and DROP algorithms.

Data mining-based materialized view and index selection in data warehouses

“…Warehouse administrators may indeed appeal to their expertise and manually provide, from a given workload, a set of candidate indexes (Frank et al, 1992;Choenni, Blanken, & Chang, 1993a, 1993b. Such a choice is however subjective.…”

“…Ascending greedy methods start from an empty set of candidate indexes (Kyu-Young, 1987;Frank et al, 1992;Choenni et al, 1993b;Chaudhuri & Narasayya, 1997). They incrementally add in indexes minimizing cost.…”

Autonomous query-driven index tuning

“…Relying on such a virtual optimization, we still have to find possibly applicable index sets. The DB2 optimizer goes one step further by providing index recommendations covering most of steps (1) to (3). Static design tools such as index wizards or advisors are built on top of the described functionality, which is also used in our approach.…”

“…There are several academic approaches as well as database products for advising index selections [5,3,15,2,17,7]. A common approach is the analysis of a workload given by the database administrator or by former queries from a log file.…”

“…The works [3,8] belong to the class of techniques which make use of a separate cost model. Relying on a stand-alone cost model has the important disadvantage, that the tool cannot exactly estimate the real system behavior.…”