Rank SQL Supporting Ranking Queries in RDBMS C
Rank. SQL: Supporting Ranking Queries in RDBMS C h e n g k a i Li (UIUC) Mohamed A. Soliman (Univ. of Waterloo) Kevin Chen-Chuan Chang (UIUC) Ihab F. Ilyas (Univ. of Waterloo)
Overview • Ranking (top-k) is an important functionality in many real -world database applications: – E-Commerce, Web Sources – Multimedia Databases – Text Retrieval, Search Engine – OLAP, Decision Support • Rank. SQL: – Support ranking as a first-class query type in RDBMS; – Integrate ranking with traditional Boolean query constructs.
Demo Query SELECT * FROM A, B, C WHERE A. j 1=B. j 1 and B. j 2=C. j 2 ORDER BY A. p 1+B. p 2+C. p 3 desc LIMIT 10; B membership dimension: Boolean predicates, Boolean function R order dimension: ranking predicates, monotonic scoring function
Rank. SQL System • Extends Postgre. SQL: – Query Engine: • Ranking Algebra • Execution engine of ranking operators • Ranking query optimizer – Front End: • Visualizing the enumeration and execution • Dataset: 3 -table join, 100, 000 tuples/table, keyforeign key join
The Differences and Insights • Differences – Traditional Plan: ~7 sec materialize-then-sort – New Plan: ~0. 8 sec Ranking is split and interleaved with Boolean query constructs. • Why? Early ranking enables – Reduced Boolean effort: cuts intermediate results for Boolean join/filter; – Reduced Ranking Effort: expensive ranking predicates can be optimized as well. 10 sort 99720 join 100000 10 rank-join 289 ranking 100000 7422 ranking 100000
Rank. SQL [SIGMOD 05] • Rank-Relational Algebra As the foundation, support splitting and interleaving at the algebra level. • Two-Dimensional Enumeration ranking (ranking predicate scheduling) and filtering (join order selection)
Welcome to our Demo Group 8 Wednesday 2 pm-3: 30 pm Friday 9 am-10: 30 am
- Slides: 7