article

Free access

Implementation of magic-sets in a relational database system

Authors:

Inderpal Singh Mumick,

Hamid PiraheshAuthors Info & Claims

ACM SIGMOD Record, Volume 23, Issue 2

Pages 103 - 114

https://doi.org/10.1145/191843.191860

Published: 24 May 1994 Publication History

Abstract

We describe the implementation of the magic-sets transformation in the Starburst extensible relational database system. To our knowledge this is the first implementation of the magic-sets transformation in a relational database system. The Starburst implementation has many novel features that make our implementation especially interesting to database practitioners (in addition to database researchers). (1) We use a cost-based heuristic for determining join orders (sips) before applying magic. (2) We push all equality and non-equality predicates using magic, replacing traditional predicate pushdown optimizations. (3) We apply magic to full SQL with duplicates, aggregation, null values, and subqueries. (4) We integrate magic with other relational optimization techniques. (5) The implementation is extensible.

Our implementation demonstrates the feasibility of the magic-sets transformation for commercial relational systems, and provides a mechanism to implement magic as an integral part of a new database system, or as an add-on to an existing database system.

References

[1]

F. Bancilhon, D. Maier, Y. Sagiv, and J. Ullman. Magic sets and other strange ways to implement logic programs. In PODS I986.

Digital Library

[2]

C. Beeri and R. Ramakrishnan. On the power of magic. Journal of Logic Programming, 10:255-300, 1991.

Digital Library

[3]

U. Dayal. Of nests and trees: A unified approach to processing queries that contain nested subqueries, aggregates, and quantifiers. In VLDB 1987.

Digital Library

[4]

M. Derr, S. Morishita, and G. Phipps. Design and implementation of the Glue-Nail database system. In SIGMOD 1993.

Digital Library

[5]

J. Fessy and B. Finance. Early experience with recursion optimization in an extensible rewriter. In Proceedings of the Fourth Australian Database Conference (ADC), 1993.

[6]

A. Gupta and I. Mumick. Magic-sets transformation in non-recursive systems. In PODS 1992.

Digital Library

[7]

R. Ganski and H. Wont. Optimization of nested SQL queries revisited. In SIGMOD 1987.

Digital Library

[8]

L. Haas, W. Chang, G. Lohman, J. McPherson, P. Wilms, G. Lapis, B. Lindsay, H. Pirahesh, M. Carey, and E. Shekita. Starburst mid-flight: As the dust clears. IEEE Transact#on# on Knowledge and Data Eng,neemng, March 1990.

Digital Library

[9]

W. Kim. On optimizing an SQL-like nested query. A CM TODS, 7(3), September 1982.

Digital Library

[10]

G. Lohman. Grammar-hke functional rules for representing query optimization alternatives. In S{GMOD 1988.

Digital Library

[11]

I. Mumick, S. Finkelstein, H. Pirahesh, and R. Ramakrishnan. Magic is relevant. In SIGMOD 1990.

Digital Library

[12]

I. Mumick, S. Finkelstein, H. Pirahesh, and R. Ramakrishnan. Magic conditions. In PODS I990.

Digital Library

[13]

K. Morris, J. Naughton, Y. Saraiya, J. Ullman, and A. Van Gelder. YAWN! (Yet Another Window on Nail!). In Data Engineering 10:4, 1987.

[14]

I. Mumick, H. Pirahesh, and R. Ramakrishnan. The magic of duplicates and aggregates. In VLDB 1990.

Digital Library

[15]

I. Mumick, H. Pirahesh, and R. Ramakrishnan. Adornments in database programs. To appear in Computers and AI, 1994. Preliminary version appeaxed in Proceedings of the International Workshop on Deductive Databa#e#, International Conference on Logic Programming, 1993.

[16]

I. Mumick. Query Optimization zn Deductive and Relational Databases. PhD thesis, Stanford University, 1991.

Digital Library

[17]

M. Muralikrishna. Improved unnesting Mgorithms for join aggregate SQL queries. In VLDB 1992.

Digital Library

[18]

S. Naqvi and S. Tsur. A Logic Language }or Data and Knowledge Bases. Computer Science Press, 1988.

Digital Library

[19]

H. Pirahesh, J. Hellerstein, and W. Hasan. Extensible/rule based query rewrite optimization in Starburst. In SIGMOD 1992.

Digital Library

[20]

R, Ramakrishnan. Magic templates: A spellbinding approach to logic programs. In Proceedings of the Fifth International Conference and Symposium on Logic Programming, 1988.

[21]

R. Ramakrishnan, D. Srivastava, and S. Sudarshan. CORAL: Control, relations and logic. In VLDB 1992.

Digital Library

[22]

P. Selinger, M. Astrahan, D. Chamberlin, R. Lone, and T. Price. Access path selection in a relational database management .system. In SIGMOD 1979.

Digital Library

[23]

F. Raab. TPC-D Working Draft 6.5. In Preparation. Transaction Processing Performance Council. February 1994.

[24]

J. Ullman. Pmnczples of Database and Knowledge- Base Systems, Volumes 1 and 2. Computer Science Press, 1989.

Digital Library

[25]

J. Vaghani, K. Ramamohanaxao, D. Kemp, Z. Somogyi, and P. Stuckey. The Aditi deductive database system. In Workshop on Deductive Databases, North American Conference on Logic Programming (NA- CLP), 1990.

Digital Library

Cited By

Lin YMehrotra S(2024)PLAQUE: Automated Predicate Learning at Query TimeProceedings of the ACM on Management of Data10.1145/36393012:1(1-25)Online publication date: 26-Mar-2024
https://dl.acm.org/doi/10.1145/3639301
Nieva SSáenz-Pérez FSánchez-Hernández J(2019)HR-SQL: Extending SQL with hypothetical reasoning and improved recursion for current database systemsInformation and Computation10.1016/j.ic.2019.104485(104485)Online publication date: Oct-2019
https://doi.org/10.1016/j.ic.2019.104485
Bonifati AFletcher GVoigt HYakovets N(2018)Querying GraphsSynthesis Lectures on Data Management10.2200/S00873ED1V01Y201808DTM05110:3(1-184)Online publication date: Oct-2018
https://doi.org/10.2200/S00873ED1V01Y201808DTM051
Show More Cited By

Index Terms

Implementation of magic-sets in a relational database system
1. Information systems
  1. Data management systems
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory
      1. Database query languages (principles)
      2. Database query processing and optimization (theory)

Recommendations

Implementation of magic-sets in a relational database system
SIGMOD '94: Proceedings of the 1994 ACM SIGMOD international conference on Management of data

We describe the implementation of the magic-sets transformation in the Starburst extensible relational database system. To our knowledge this is the first implementation of the magic-sets transformation in a relational database system. The Starburst ...
Relational Database Design and Implementation
Implementation of object-relational DBMSs in a relational database course

Object-relational DBMS was gradually added as a new topic to the author's database course in response to the rapid changes in DBMS technology in the real world. Implementation of ORDBMS technology in a traditional relational database course had ...

Comments

Information & Contributors

Information

Published In

cover image ACM SIGMOD Record

ACM SIGMOD Record Volume 23, Issue 2

June 1994

522 pages

ISSN:0163-5808

DOI:10.1145/191843

Editors:
Richard Thomas Snodgrass
Univ. of Arizona
,
Marianne Winslett
Univ. of Illinois

Issue’s Table of Contents

SIGMOD '94: Proceedings of the 1994 ACM SIGMOD international conference on Management of data
May 1994
525 pages
ISBN:0897916395
DOI:10.1145/191839
Editors:
Richard Thomas Snodgrass
Univ. of Arizona
,
Marianne Winslett
Univ. of Illinois

Copyright © 1994 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 24 May 1994

Published in SIGMOD Volume 23, Issue 2

Check for updates

Qualifiers

Article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

49
Total Citations
View Citations
825
Total Downloads

Downloads (Last 12 months)97
Downloads (Last 6 weeks)13

Reflects downloads up to 30 Aug 2024

Other Metrics

View Author Metrics

Citations

Cited By

Lin YMehrotra S(2024)PLAQUE: Automated Predicate Learning at Query TimeProceedings of the ACM on Management of Data10.1145/36393012:1(1-25)Online publication date: 26-Mar-2024
https://dl.acm.org/doi/10.1145/3639301
Nieva SSáenz-Pérez FSánchez-Hernández J(2019)HR-SQL: Extending SQL with hypothetical reasoning and improved recursion for current database systemsInformation and Computation10.1016/j.ic.2019.104485(104485)Online publication date: Oct-2019
https://doi.org/10.1016/j.ic.2019.104485
Bonifati AFletcher GVoigt HYakovets N(2018)Querying GraphsSynthesis Lectures on Data Management10.2200/S00873ED1V01Y201808DTM05110:3(1-184)Online publication date: Oct-2018
https://doi.org/10.2200/S00873ED1V01Y201808DTM051
Maier DTekle KKifer MWarren D(2018)DatalogDeclarative Logic Programming10.1145/3191315.3191317(3-100)Online publication date: 1-Sep-2018
https://dl.acm.org/doi/10.1145/3191315.3191317
Ordonez CCabrera WGurram A(2017)Comparing columnar, row and array DBMSs to process recursive queries on graphsInformation Systems10.1016/j.is.2016.04.00663(66-79)Online publication date: Jan-2017
https://doi.org/10.1016/j.is.2016.04.006
Ordonez CGurram ARai NSong ISimitsis ACuzzocrea A(2014)Recursive Query Evaluation in a Column DBMS to Analyze Large GraphsProceedings of the 17th International Workshop on Data Warehousing and OLAP10.1145/2666158.2666177(71-80)Online publication date: 7-Nov-2014
https://dl.acm.org/doi/10.1145/2666158.2666177
Sequeda JMiranker D(2013)UltrawrapWeb Semantics: Science, Services and Agents on the World Wide Web10.1016/j.websem.2013.08.00222(19-39)Online publication date: 1-Oct-2013
https://dl.acm.org/doi/10.1016/j.websem.2013.08.002
Aranda GNieva SSáenz-Pérez FSánchez-Hernández J(2013)Formalizing a Broader Recursion Coverage in SQLProceedings of the 15th International Symposium on Practical Aspects of Declarative Languages - Volume 775210.1007/978-3-642-45284-0_7(93-108)Online publication date: 21-Jan-2013
https://dl.acm.org/doi/10.1007/978-3-642-45284-0_7
Schüller GSaul RBehrend A(2012)AIMSProceedings of the 24th international conference on Scientific and Statistical Database Management10.1007/978-3-642-31235-9_47(622-627)Online publication date: 25-Jun-2012
https://dl.acm.org/doi/10.1007/978-3-642-31235-9_47
Badia A(2003)Computing SQL Queries with Boolean AggregatesData Warehousing and Knowledge Discovery10.1007/978-3-540-45228-7_39(391-400)Online publication date: 2003
https://doi.org/10.1007/978-3-540-45228-7_39
Show More Cited By

View Options

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

Media

Figures

Other

Tables

View Issue’s Table of Contents