Index selection in relational databases

Choenni, Sunil; Blanken, Henk; Chang, Thiel

doi:10.1109/icci.1993.315323

Cited by 22 publications

(12 citation statements)

References 5 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Then, at each iteration, indexes are pruned (Kyu-Young, 1987;Choenni et al, 1993a). If workload cost before pruning is lower (respectively, greater) than workload cost after pruning, the pruned indexes are useless (respectively, useful) for reducing cost.…”

Section: Index Selectionmentioning

confidence: 99%

See 1 more Smart Citation

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

Section: Index Selectionmentioning

confidence: 99%

“…Data access is performed in two steps: scanning the index' bitmaps, and then reading the tuples. If access to the bitmaps is direct and data are uniformly distributed, which is a reasonable assumption according to Choenni et al (1993a), index traversal cost is d…”

Section: Cost Modelsmentioning

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

show abstract

“…In order to reduce the solution search space, already in [7] some simple heuristics have been proposed. Several papers [2,9,14,29] recognize ISP as a variation of a well-known Knapsack Problem and propose some heuristic methods to solve it. However, none of them uses a query plan optimizer to estimate gains of the selected indexes nor to propose index candidates.…”

Section: Related Workmentioning

confidence: 99%

Automatic Index Selection in RDBMS by Exploring Query Execution Plan Space

Kołaczkowski

Rybiński

2009

Advances in Data Management

View full text Add to dashboard Cite

A novel approach to solving Index Selection Problem (ISP) is presented. In contrast to other known ISP approaches, our method searches the space of possible query execution plans, instead of searching the space of index configurations. An evolutionary algorithm is used for searching. The solution is obtained indirectly as the set of indexes used by the best query execution plans. The method has important features over other known algorithms: (1) it converges to the optimal solution, unlike greedy heuristics, which for performance reasons tend to reduce the space of candidate solutions, possibly discarding optimal solutions; (2) though the search space is huge and grows exponentially with the size of the input workload, searching the space of the query plans allows to direct more computational power to the most costly plans, thus yielding very fast convergence to "good enough" solutions; and (3) the costly reoptimization of the workload is not needed for calculating the objective function, so several thousands of candidates can be checked in a second. The algorithm was tested for large synthetic and real-world SQL workloads to evaluate the performace and scalability.

show abstract

“…Most of these initiatives have commercial purpose, though, which prevents them to be extended for general purpose. Other approaches have suggested to take from the DBMS the responsibility for selecting indexes, letting this assignment to be done by DBMS-external tools [5]. This approach may lead a tool to calculate operation costs differently of the cost model implemented by the DBMS.…”

Section: Introductionmentioning

confidence: 99%

A Tool for Automatic Index Selection in Database Management Systems

Pedrozo

Vaz

2014

2014 International Symposium on Computer, Consumer and Control

View full text Add to dashboard Cite

A task commonly undertaken by database administrators to speed up the performance of commands submitted to a DBMS is the selection of indexes for the tables. This paper introduces the concept of how to use the Database Management System (DBMS) Optimizer to select their own indexes. We introduce a heuristic to guide the index selection process, which is implemented by a tool called AISIO that runs integrated to the DBMS. Our tool captures Structured Query Language (SQL) statements submitted to the DBMS, analyzes them and, based on the implemented heuristic and on the DBMS Optimizer statistics, recommends which indexes could potentially optimize the performance of those transactions. The tool also provides a measure of the performance gains obtained from the use of the recommended indexes. To illustrate our tool, we integrate it to the PostgreSQL DBMS and experimental results are presented to evidence our contributions.

show abstract

Index selection in relational databases

Cited by 22 publications

References 5 publications

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

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

Automatic Index Selection in RDBMS by Exploring Query Execution Plan Space

A Tool for Automatic Index Selection in Database Management Systems

Contact Info

Product

Resources

About