Adaptive rank-aware query optimization in relational databases

Ihab F. Ilyas, Walid G. Aref, Ahmed Elmagarmid, Hicham G. Elmongui, Rahul Shah, Jeffrey Scott Vitter

Research output: Contribution to journalArticle

38 Citations (Scopus)

Abstract

Rank-aware query processing has emerged as a key requirement in modern applications. In these applications, efficient and adaptive evaluation of top-k queries is an integral part of the application semantics. In this article, we introduce a rank-aware query optimization framework that fully integrates rank-join operators into relational query engines. The framework is based on extending the System R dynamic programming algorithm in both enumeration and pruning. We define ranking as an interesting physical property that triggers the generation of rank-aware query plans. Unlike traditional join operators, optimizing for rank-join operators depends on estimating the input cardinality of these operators. We introduce a probabilistic model for estimating the input cardinality, and hence the cost of a rank-join operator. To our knowledge, this is the first effort in estimating the needed input size for optimal rank aggregation algorithms. Costing ranking plans is key to the full integration of rank-join operators in real-world query processing engines.Since optimal execution strategies picked by static query optimizers lose their optimality due to estimation errors and unexpected changes in the computing environment, we introduce several adaptive execution strategies for top-k queries that respond to these unexpected changes and costing errors. Our reactive reoptimization techniques change the execution plan at runtime to significantly enhance the performance of running queries. Since top-k query plans are usually pipelined and maintain a complex ranking state, altering the execution strategy of a running ranking query is an important and challenging task.We conduct an extensive experimental study to evaluate the performance of the proposed framework. The experimental results are twofold: (1) we show the effectiveness of our cost-based approach of integrating ranking plans in dynamic programming cost-based optimizers; and (2) we show a significant speedup (up to 300%) when using our adaptive execution of ranking plans over the state-of-the-art mid-query reoptimization strategies.

Original languageEnglish
Pages (from-to)1257-1304
Number of pages48
JournalACM Transactions on Database Systems
Volume31
Issue number4
DOIs
Publication statusPublished - 1 Dec 2006
Externally publishedYes

Fingerprint

Query processing
Dynamic programming
Engines
Costs
Error analysis
Mathematical operators
Agglomeration
Physical properties
Semantics
Statistical Models

Keywords

  • Adaptive processing
  • Advanced query processing
  • Rank-aware optimization
  • Ranking
  • Top-k

ASJC Scopus subject areas

  • Information Systems
  • Computer Graphics and Computer-Aided Design
  • Software

Cite this

Adaptive rank-aware query optimization in relational databases. / Ilyas, Ihab F.; Aref, Walid G.; Elmagarmid, Ahmed; Elmongui, Hicham G.; Shah, Rahul; Vitter, Jeffrey Scott.

In: ACM Transactions on Database Systems, Vol. 31, No. 4, 01.12.2006, p. 1257-1304.

Research output: Contribution to journalArticle

Ilyas, Ihab F. ; Aref, Walid G. ; Elmagarmid, Ahmed ; Elmongui, Hicham G. ; Shah, Rahul ; Vitter, Jeffrey Scott. / Adaptive rank-aware query optimization in relational databases. In: ACM Transactions on Database Systems. 2006 ; Vol. 31, No. 4. pp. 1257-1304.
@article{18cadab1fcf1421e91ca1720f2dab6c7,
title = "Adaptive rank-aware query optimization in relational databases",
abstract = "Rank-aware query processing has emerged as a key requirement in modern applications. In these applications, efficient and adaptive evaluation of top-k queries is an integral part of the application semantics. In this article, we introduce a rank-aware query optimization framework that fully integrates rank-join operators into relational query engines. The framework is based on extending the System R dynamic programming algorithm in both enumeration and pruning. We define ranking as an interesting physical property that triggers the generation of rank-aware query plans. Unlike traditional join operators, optimizing for rank-join operators depends on estimating the input cardinality of these operators. We introduce a probabilistic model for estimating the input cardinality, and hence the cost of a rank-join operator. To our knowledge, this is the first effort in estimating the needed input size for optimal rank aggregation algorithms. Costing ranking plans is key to the full integration of rank-join operators in real-world query processing engines.Since optimal execution strategies picked by static query optimizers lose their optimality due to estimation errors and unexpected changes in the computing environment, we introduce several adaptive execution strategies for top-k queries that respond to these unexpected changes and costing errors. Our reactive reoptimization techniques change the execution plan at runtime to significantly enhance the performance of running queries. Since top-k query plans are usually pipelined and maintain a complex ranking state, altering the execution strategy of a running ranking query is an important and challenging task.We conduct an extensive experimental study to evaluate the performance of the proposed framework. The experimental results are twofold: (1) we show the effectiveness of our cost-based approach of integrating ranking plans in dynamic programming cost-based optimizers; and (2) we show a significant speedup (up to 300{\%}) when using our adaptive execution of ranking plans over the state-of-the-art mid-query reoptimization strategies.",
keywords = "Adaptive processing, Advanced query processing, Rank-aware optimization, Ranking, Top-k",
author = "Ilyas, {Ihab F.} and Aref, {Walid G.} and Ahmed Elmagarmid and Elmongui, {Hicham G.} and Rahul Shah and Vitter, {Jeffrey Scott}",
year = "2006",
month = "12",
day = "1",
doi = "10.1145/1189769.1189772",
language = "English",
volume = "31",
pages = "1257--1304",
journal = "ACM Transactions on Database Systems",
issn = "0362-5915",
publisher = "Association for Computing Machinery (ACM)",
number = "4",

}

TY - JOUR

T1 - Adaptive rank-aware query optimization in relational databases

AU - Ilyas, Ihab F.

AU - Aref, Walid G.

AU - Elmagarmid, Ahmed

AU - Elmongui, Hicham G.

AU - Shah, Rahul

AU - Vitter, Jeffrey Scott

PY - 2006/12/1

Y1 - 2006/12/1

N2 - Rank-aware query processing has emerged as a key requirement in modern applications. In these applications, efficient and adaptive evaluation of top-k queries is an integral part of the application semantics. In this article, we introduce a rank-aware query optimization framework that fully integrates rank-join operators into relational query engines. The framework is based on extending the System R dynamic programming algorithm in both enumeration and pruning. We define ranking as an interesting physical property that triggers the generation of rank-aware query plans. Unlike traditional join operators, optimizing for rank-join operators depends on estimating the input cardinality of these operators. We introduce a probabilistic model for estimating the input cardinality, and hence the cost of a rank-join operator. To our knowledge, this is the first effort in estimating the needed input size for optimal rank aggregation algorithms. Costing ranking plans is key to the full integration of rank-join operators in real-world query processing engines.Since optimal execution strategies picked by static query optimizers lose their optimality due to estimation errors and unexpected changes in the computing environment, we introduce several adaptive execution strategies for top-k queries that respond to these unexpected changes and costing errors. Our reactive reoptimization techniques change the execution plan at runtime to significantly enhance the performance of running queries. Since top-k query plans are usually pipelined and maintain a complex ranking state, altering the execution strategy of a running ranking query is an important and challenging task.We conduct an extensive experimental study to evaluate the performance of the proposed framework. The experimental results are twofold: (1) we show the effectiveness of our cost-based approach of integrating ranking plans in dynamic programming cost-based optimizers; and (2) we show a significant speedup (up to 300%) when using our adaptive execution of ranking plans over the state-of-the-art mid-query reoptimization strategies.

AB - Rank-aware query processing has emerged as a key requirement in modern applications. In these applications, efficient and adaptive evaluation of top-k queries is an integral part of the application semantics. In this article, we introduce a rank-aware query optimization framework that fully integrates rank-join operators into relational query engines. The framework is based on extending the System R dynamic programming algorithm in both enumeration and pruning. We define ranking as an interesting physical property that triggers the generation of rank-aware query plans. Unlike traditional join operators, optimizing for rank-join operators depends on estimating the input cardinality of these operators. We introduce a probabilistic model for estimating the input cardinality, and hence the cost of a rank-join operator. To our knowledge, this is the first effort in estimating the needed input size for optimal rank aggregation algorithms. Costing ranking plans is key to the full integration of rank-join operators in real-world query processing engines.Since optimal execution strategies picked by static query optimizers lose their optimality due to estimation errors and unexpected changes in the computing environment, we introduce several adaptive execution strategies for top-k queries that respond to these unexpected changes and costing errors. Our reactive reoptimization techniques change the execution plan at runtime to significantly enhance the performance of running queries. Since top-k query plans are usually pipelined and maintain a complex ranking state, altering the execution strategy of a running ranking query is an important and challenging task.We conduct an extensive experimental study to evaluate the performance of the proposed framework. The experimental results are twofold: (1) we show the effectiveness of our cost-based approach of integrating ranking plans in dynamic programming cost-based optimizers; and (2) we show a significant speedup (up to 300%) when using our adaptive execution of ranking plans over the state-of-the-art mid-query reoptimization strategies.

KW - Adaptive processing

KW - Advanced query processing

KW - Rank-aware optimization

KW - Ranking

KW - Top-k

UR - http://www.scopus.com/inward/record.url?scp=33846192864&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=33846192864&partnerID=8YFLogxK

U2 - 10.1145/1189769.1189772

DO - 10.1145/1189769.1189772

M3 - Article

AN - SCOPUS:33846192864

VL - 31

SP - 1257

EP - 1304

JO - ACM Transactions on Database Systems

JF - ACM Transactions on Database Systems

SN - 0362-5915

IS - 4

ER -