RankSQL

Abstract

This paper introduces RankSQL, a system that provides a systematic and principled framework to support efficient evaluations of ranking (top-k) queries in relational database systems (RDBMS), by extending relational algebra and query optimization. Previously, top-k query processing is studied in the middleware scenario or in RDBMS in a "piecemeal" fashion, i.e., focusing on specific operator or sitting outside the core of query engines. In contrast, we aim to support ranking as a first-class database construct. As a key insight, the new ranking relationship can be viewed as another logical property of data, parallel to the "membership" property of relational data model. While membership is essentially supported in RDBMS, the same support for ranking is clearly lacking. We address the fundamental integration of ranking in RDBMS in a way similar to how membership, i.e., Boolean filtering, is supported. We extend relational algebra by proposing a rank-relational model to capture the ranking property, and introducing new and extended operators to support ranking as a first-class construct. Enabled by the extended algebra, we present a pipelined and incremental execution model of ranking query plans (that cannot be expressed traditionally) based on a fundamental ranking principle. To optimize top-k queries, we propose a dimensional enumeration algorithm to explore the extended plan space by enumerating plans along two dual dimensions: ranking and membership. We also propose a sampling-based method to estimate the cardinality of rank-aware operators, for costing plans. Our experiments show the validity of our framework and the accuracy of the proposed estimation model.Example 1: Consider user Amy, who wants to plan her trip to Chicago. She wants to stay in a hotel, have lunch in an Italian restaurant (condition c1: r.cuisine=Italian), and walk to a museum after lunch; the hotel and the restaurant together should cost less than $100 (c2: h.price+r.price<100); the museum and the restaurant should be in the same area (c3: r.area=m.area). Further, to rank the qualified results, she specifies several ranking criteria, or "predicates"-for low hotel price, with p1: cheap(h.price); for close distance between the hotel and the restaurant, with p2: close(h.addr, r.addr); and for matching her interests with the museum's collections, with p3: related (m.collection, "dinosaur"). These ranking predicates return numeric scores and the overall scoring function sums up their values. The query is shown below in PostgreSQL syntax. SELECT * FROMHotel h, Restaurant r, Museum m WHERE c1 AND c2 AND c3 ORDER BY p1 + p2 + p3 LIMIT kWith current relational query processing capabilities, the only way to execute the previous query is to: (1) consume all the records of the three inputs; (2) join the three inputs and materialize the whole join results; (3) evaluate the three predicates p1, p2, and p3 for each valid join result; (4) sort the join results on p1 + p2 + p3;

Efficient Retrieval of the Top-k Most Relevant Event-Partner Pairs

“…Ranking (top-k) queries have also been integrated into relational database systems [33], [34]. Mamoulis et al [35] identify two phases that any (non-random access) NRA algorithm should go through: a growing phase and a shrinking phase.…”

Interactive Search Processes in Complex Work Situations : a Retrieval Framework

“…A similar approach which tries to introduce an extended relational algebra and necessary query optimization is presented in [Li et al, 2005] with the RankSQL approach. In particular, the authors aim at including the ranking functionality of top-k queries as a database operator and not a solution which sits outside of the query engine.…”

A Cost-Based Range Estimation for Mapping Top-k Selection Queries over Relational Databases

“…In rank-aware query optimization algorithms (Ilyas, Aref & Elmagarmid, 2002Ilyas et al, 2006;Li, Chang, Ilyas & Song, 2005), ranking is treated as a first-class functionality that would be fully incorporated in the query engine. Ilyas et al (2002Ilyas et al ( , 2003 introduced a set of logical and physical rank-join operators to enable relational query processors with ranking capabilities and avoid the naïve materialize-then-sort approach.…”

“…Ilyas et al (2002Ilyas et al ( , 2003 introduced a set of logical and physical rank-join operators to enable relational query processors with ranking capabilities and avoid the naïve materialize-then-sort approach. Li et al (2005) proposed an extended rank-aware relational algebra to integrate rank aware operators in query engine. Ilyas et al (2006) further extended this approach by providing cost models and adaptive optimization techniques for rankjoin operators that respond to cost estimation errors and unexpected changes in computing environment.…”