1 / 9

CS240A: Databases and Knowledge Bases Recursive Queries in SQL 1999

This article provides an overview of recursive queries in SQL:1999 and discusses their implementation and limitations. Topics covered include the recursive union, the WITH construct, differential fixpoint improvement, and recursive rule processing.

rolive
Télécharger la présentation

CS240A: Databases and Knowledge Bases Recursive Queries in SQL 1999

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS240A: Databases and Knowledge BasesRecursive Queries in SQL 1999 Notes From Chapter 9 of Advanced Database Systems by Zaniolo, Ceri, Faloutsos, Snodgrass, Subrahmanian and Zicari Morgan Kaufmann, 1997 Carlo Zaniolo Department of Computer Science University of California, Los Angeles December, 2001

  2. The Part/Subparts Exampleassembly(PART, SUBPART, QTY) CREATE RECURSIVE VIEW all_subparts(Major, Minor) AS SELECT PART SUBPART FROM assembly UNION SELECT all.Major assb.SUBPART FROM all_subparts AS all, assembly AS assb WHERE all.Minor= assb.PART • This is called a recursive union. • We have the union of an Exit Select and a Recursive Select • This definition corresponds to left-linear recursive Datalog rules • Materialization of the recursive view from the previous Example SELECT * FROM all_subparts

  3. The WITH Construct • The WITH construct provides another way, and a more direct one to express recursion in SQL:1999 • Query: Find the parts using top tube WITH RECURSIVE all_super(Major, Minor) AS (SELECT PART, SUBPART FROM assembly UNION SELECT assb.PART, all.Minor FROM assembly AS assb, all_super AS all WHERE assb.SUBPART = all.Major) SELECT * WHERE Minor = 'top_tube‘ • this corresponds to a right-linear Datalog rule

  4. Implementation CREATE RECURSIVE VIEW all_subparts(Major, Minor) AS SELECT PART SUBPART FROM assembly UNION SELECT all.Major assb.SUBPART FROM all_subparts AS all, assembly AS assb WHERE all.Minor= assb.PART • To implement the differential fixpoint improvement we replace the recursive relation all_subparts in the FROM clause by all_subparts, where all_subparts contains the new tuples generated in the previous iteration of differential fixpoint Algorithm. (But also eliminate duplicates as they are generated)

  5. Find the parts using top tube(on Right­Linear Recursion) WITH RECURSIVE all_super(Major, Minor) AS (SELECT PART, SUBPART FROM assembly UNION SELECT assb.PART, all.Minor FROM assembly AS assb, all_super AS all WHERE assb.SUBPART = all.Major) SELECT * FROM all_subparts WHERE Minor = 'top tube‘ Add Minor= 'top tube', to the WHERE clauses in exit select and the recursive select: WITH RECURSIVE all_super(Major, Minor) AS (SELECT PART, SUBPART FROM assembly WHERE SUBPART = 'top tube' UNION SELECT assb.PART, all.Minor FROM assembly AS assb, all_super AS all WHERE assb.SUBPART = all.Major AND WHERE all.Minor = 'top tube‘) SELECT * FROM all_subparts

  6. Implementation left-recursion and right-recursion SELECT * FROM_all subparts WHERE Minor = 'top tube' Expressed against the virtual view of CREATE RECURSIVE view all_subparts(Major, Minor) AS SELECT PART SUBPART FROM assembly UNION SELECT all.Major AS assb.SUBPART FROM all_subparts AS all, assembly AS assb WHERE all.Minor= assb.PART The addition of the condition Minor = 'top tube' to the recursive select would not produce an equivalent query. Thus, the compiler transforms the recursive select into its right­linear equivalent , then adds Minor ='top tube' to the WHERE clause.

  7. Recursive Rule Processing in SQL • In general the compilation techniques usable for such transformations are basically those previously described for Datalog. Including the magic set transformation. • Also stratification w.r.t. negation and aggregates* is required in SQL:1999 • Mutual recursion is supported and this where WITH becomes indispensable. ---------* Because aggregates are nonmonotonic!

  8. Recursive Rule Processing in SQL • In general, the compilation techniques usable for such transformations are basically those previously described for Datalog---Including the magic set transformation • Also stratification w.r.t. `negation’ and aggregates is required by SQL:1999. • By negation, we mean NOT EXIST and EXCEPT because conditions such as NOT(A>B) become A<=B. • That means that you cannot express several graph algorithms in SQL:1999

  9. Discussion • Datalog contributions • Power (beyond SQL-2) • Rule-based programming paradigm very effective in many applications • Simple Formal semantics • Technology • Impact on SQL:1999 • Recursive queries now supported in commercial DBMSs • Limited used in applications, because hard to write and also restrictions imposed by stratification.

More Related