Near optimal multiple choice index selection for relational databases

Gündem, Taflan İmre

doi:10.1016/s0898-1221(98)00256-9

Cited by 4 publications

(4 citation statements)

References 13 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Other models uses the Knapsack problem as a vehicle for finding an optimal solution [24] and more exotic approaches use genetic algorithms [27] to find sub-optimal indices. The work of [22] proposes an extension of prior index optimisation models where there are multiple-candidates available for attributes, and Gündem shows that the optimisation model is NP-hard, and provide an approximation algorithm which is bounded by a logarithmic time order. Older works [36,13] provide some probabilistic modelling for selecting secondary indices and some ad-hoc approaches.…”

Section: Index Selectionmentioning

confidence: 99%

Optimal On The Fly Index Selection in Polynomial Time

Jordan,

Scholz,

Subotić

2017

Preprint

View full text Add to dashboard Cite

The index selection problem (ISP) is an important problem for accelerating the execution of relational queries, and it has received a lot of attention as a combinatorial knapsack problem in the past. Various solutions to this very hard problem have been provided. In contrast to existing literature, we change the underlying assumptions of the problem definition: we adapt the problem for systems that store relations in memory, and use complex specification languages, e.g., Datalog. In our framework, we decompose complex queries into primitive searches that select tuples in a relation for which an equality predicate holds. A primitive search can be accelerated by an index exhibiting a worst-case run-time complexity of log-linear time in the size of the output result of the primitive search. However, the overheads associated with maintaining indexes are very costly in terms of memory and computing time.In this work, we present an optimal polynomial-time algorithm that finds the minimal set of indexes of a relation for a given set of primitive searches. An index may cover more than one primitive search due to the algebraic properties of the search predicate, which is a conjunction of equalities over the attributes of a relation. The index search space exhibits a complexity of O(2 m m) where m is the number of attributes in a relation, and, hence brute-force algorithms searching for solutions in the index domain are infeasible. As a scaffolding for designing a polynomial-time algorithm, we build a partial order on search operations and use a constructive version of Dilworth's theorem. We show a strong relationship between chains of primitive searches (forming a partial order) and indexes. We demonstrate the effectiveness and efficiency of our algorithm for an in-memory Datalog compiler that is able to process relations with billions of entries in memory.

show abstract

Section: Index Selectionmentioning

confidence: 99%

Optimal On The Fly Index Selection in Polynomial Time

Jordan,

Scholz,

Subotić

2017

Preprint

View full text Add to dashboard Cite

show abstract

“…The combinatory construction of an index configuration is realized through the crossover, mutation and selection genetic operators. Eventually, the index selection problem has also been formulated in several studies as a knapsack problem (Ip, Saxton, & Raghavan, 1983;Gundem, 1999;Valentin et al, 2000;Feldman & Reouven, 2003) where indexes are objects, index storage costs represent object weights, workload cost is the benefit function, and storage space is knapsack size.…”

Section: Index Selectionmentioning

confidence: 99%

Section: Index Selectionmentioning

confidence: 99%

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

Aouiche¹,

Darmont²

2009

J Intell Inf Syst

View full text Add to dashboard Cite

Materialized views and indexes are physical structures for accelerating data access that are casually used in data warehouses. However, these data structures generate some maintenance overhead. They also share the same storage space. Most existing studies about materialized view and index selection consider these structures separately. In this paper, we adopt the opposite stance and couple materialized view and index selection to take view-index interactions into account and achieve efficient storage space sharing. Candidate materialized views and indexes are selected through a data mining process. We also exploit cost models that evaluate the respective benefit of indexing and view materialization, and help select a relevant configuration of indexes and materialized views among the candidates. Experimental results show that our strategy performs better than an independent selection of materialized views and indexes.

show abstract

“…Descending methods start with the whole set of candidate indexes and prune indexes until workload cost increases (Kyu-Young, 1987;Choenni et al, 1993a). Classical optimization algorithms have also been used to solve this problem, such as knapsack resolution (Ip et al, 1983;Gündem, 1999;Valentin et al, 2000;Feldman & Reouven, 2003) and genetic algorithms (Kratika et al, 2003).…”

Section: Index Selection Problemmentioning

confidence: 99%

Index and Materialized View Selection in Data Warehouses

Aouiche

Darmont

2009

Handbook of Research on Innovations in Database Technologies and Applications

View full text Add to dashboard Cite

INTRODUCTIONDatabase Management Systems (DBMSs) require an administrator, whose principal tasks are data management, both at the logical and physical levels, as well as performance optimization. With the wide development of databases and data warehouses, minimizing the administration function is crucial. This function includes the selection of suitable physical structures to improve system performance.View materialization and indexing are presumably some of the most effective optimization techniques adopted in relational implementations of data warehouses. Materialized views are physical structures that improve data access time by precomputing intermediary results.Therefore, end-user queries can be efficiently processed through data stored in views and do not need to access the original data. Indexes are also physical structures that allow direct data access. They avoid sequential scans and thereby reduce query response time. Nevertheless, these solutions require additional storage space and entail maintenance overhead. The issue is then to select an appropriate configuration of materialized views and indexes that minimizes both query response time and maintenance cost, given a limited storage space. This problem is NP-hard (Gupta & Mumick, 2005).The aim of this article is to present an overview of the major families of state-of-the-art index and materialized view selection methods; and to discuss the issues and future trends in data warehouse performance optimization. We particularly focus on data mining-based heuristics we developed to reduce the selection problem complexity and target the most pertinent candidate indexes and materialized views. BACKGROUND

show abstract

Near optimal multiple choice index selection for relational databases

Cited by 4 publications

References 13 publications

Optimal On The Fly Index Selection in Polynomial Time

Optimal On The Fly Index Selection in Polynomial Time

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

Index and Materialized View Selection in Data Warehouses

Contact Info

Product

Resources

About