US5724570A
Expired / Lapsed
US
⏱️ 0.0 years remaining
Method and apparatus for a complete SQL subquery elimination process
Assignee: Hewlett Packard Development Co LP
•
Filing: 1995-06-07
•
Granted: 1998-03-03
•
Expires: 2015-06-07
Abstract
Abstract A method for subquery elimination for a relational model database based on the SQL language running a database management system (DBMS) under the TANDEM NONSTOP (tm) operating kernel. A series of seven transformation rules are applied to eliminate nested queries in the SQL language. The method and teachings of the present invention generalizes to all types of nested subqueries in SQL, allowing for more efficient computations, allocation and maintenance of system resources.
Inventors
- 👤 Hansjorg Zeller
- 👤 Pedro Celis
Bibliographic Details
Application Number
—
Jurisdiction
US
Data Source
google_patents
Last Synchronized
2026-11-03 09:58
CPC Classifications
G; G06; G06F; G06F16/00; G06F16/20; G06F16/24; G06F16/245; G06F16/2453; G06F16/24534; G06F16/24535; Y; Y10; Y10S; Y10S707/00; Y10S707/99931; Y10S707/99933; Y10S707/99934
Claims Analysis
Total Claims: 30
Claim 1
Independent Claim
150 words
A method for the transformation of a nested SQL query to eliminate all subqueries of the nested SQL query, the method comprising the steps, performed by a computer processor, of: representing in a memory a nested SQL language query including at least one subquery in a first dataflow tree diagram with nodes of expressional operators and relational operators; eliminating each of said subqueries of said representing step according to a plurality of predetermined transformation rules being selected from a group of SQL transformation rules including the normalization rules NR1, NR2, NR3, NR4, NR5, NR6, NR7, said eliminating step transforming said nested SQL language query of said first dataflow tree diagram in the memory to a normalized and syntactically transformed equivalent second dataflow tree diagram, so that said relational operators in the second dataflow tree depend from one another and each of said relational operators are not isolated between expressional operators.
Claim 2
Independent Claim
150 words
A method for the transformation of a nested SQL query to eliminate all subqueries of the nested SQL query, the method comprising the steps, performed by a computer processor, of: representing in a memory a nested SQL language query including at least one subquery in a first dataflow tree diagram with nodes of expressional operators and relational operators; eliminating each of said subqueries of said representing step according to a plurality of predetermined transformation rules being selected from a group of SQL transformation rules including the normalization rules NR1, NR2, NR3, NR4, NR5, NR6, NR7, said eliminating step transforming said nested SQL language query of said first dataflow tree diagram in the memory to a normalized and syntactically transformed equivalent second dataflow tree diagram, so that said relational operators in the second dataflow tree depend from one another and each of said relational operators are not isolated between expressional operators.
Claim 3
Independent Claim
144 words
A method for the transformation of a nested SQL language query to eliminate all subqueries of the nested SQL query, the method comprising the steps, performed by a computer processor, of: representing in a memory a nested SQL language query in a dataflow tree diagram with nodes of expressional operators and relational operators; and transforming said SQL language dataflow tree diagram in the memory according to a plurality of predetermined rules of transformation being selected from a group of SQL transformation rules including the normalization rules NR3, NR4, NR5, NR6, NR7, so that said relational operators in the transformed dataflow tree depend from one another and are not isolated between expressional operators, and at least one of the normalization rules eliminates all subqueries inside a relational node, wherein each of the subqueries is allowed to reference values generated by a child of the node.
Claim 4
Independent Claim
144 words
A method for the transformation of a nested SQL language query to eliminate all subqueries of the nested SQL query, the method comprising the steps, performed by a computer processor, of: representing in a memory a nested SQL language query in a dataflow tree diagram with nodes of expressional operators and relational operators; and transforming said SQL language dataflow tree diagram in the memory according to a plurality of predetermined rules of transformation being selected from a group of SQL transformation rules including the normalization rules NR3, NR4, NR5, NR6, NR7, so that said relational operators in the transformed dataflow tree depend from one another and are not isolated between expressional operators, and at least one of the normalization rules eliminates all subqueries inside a relational node, wherein each of the subqueries is allowed to reference values generated by a child of the node.
Claim 5
Independent Claim
146 words
A method for the transformation of a nested SQL language query by a computer processor running a database management system to eliminate all nested subqueries, the method comprising the steps, performed by a computer processor, of; representing in a memory a nested SQL language query in a dataflow tree diagram with nodes of expressional operators and relational operators; and transforming said SQL language dataflow tree diagram in the memory according to a plurality of predetermined rules of transformation being selected from a group of SQL transformation rules including the normalization rules NR3, NR4, NR5, NR6, NR7, so that said relational operators in the transformed dataflow tree depend from one another and are not isolated between expressional operators, and at least one of the normalization rules eliminates all subqueries inside a relational node, wherein each of the subqueries is allowed to reference values generated by the node.
Claim 6
Independent Claim
146 words
A method for the transformation of a nested SQL language query by a computer processor running a database management system to eliminate all nested subqueries, the method comprising the steps, performed by a computer processor, of; representing in a memory a nested SQL language query in a dataflow tree diagram with nodes of expressional operators and relational operators; and transforming said SQL language dataflow tree diagram in the memory according to a plurality of predetermined rules of transformation being selected from a group of SQL transformation rules including the normalization rules NR3, NR4, NR5, NR6, NR7, so that said relational operators in the transformed dataflow tree depend from one another and are not isolated between expressional operators, and at least one of the normalization rules eliminates all subqueries inside a relational node, wherein each of the subqueries is allowed to reference values generated by the node.
Claim 7
Independent Claim
150 words
A method for the transformation of a nested SQL language query to eliminate all nested subqueries, the method comprising the steps, performed by a computer processor, of; representing in a memory a nested SQL language query in a dataflow tree diagram with nodes of expressional operators and relational operators; and transforming said SQL language dataflow tree diagram in the memory according to a plurality of predetermined rules of transformation being selected from a group of SQL transformation rules including the normalization rules NR3, NR4, NR5, NR6, NR7, so that said relational operators in the transformed dataflow tree depend from one another and are not isolated between expressional operators, and at least one of the normalization rules eliminates each of the subqueries inside an "input" node, where an input node is defined as a node that passes a result of at least one of the subqueries down to its child node.
Claim 8
Independent Claim
150 words
A method for the transformation of a nested SQL language query to eliminate all nested subqueries, the method comprising the steps, performed by a computer processor, of; representing in a memory a nested SQL language query in a dataflow tree diagram with nodes of expressional operators and relational operators; and transforming said SQL language dataflow tree diagram in the memory according to a plurality of predetermined rules of transformation being selected from a group of SQL transformation rules including the normalization rules NR3, NR4, NR5, NR6, NR7, so that said relational operators in the transformed dataflow tree depend from one another and are not isolated between expressional operators, and at least one of the normalization rules eliminates each of the subqueries inside an "input" node, where an input node is defined as a node that passes a result of at least one of the subqueries down to its child node.
Claim 9
Independent Claim
85 words
An apparatus for the elimination of SQL subqueries, comprising: a parser provided to convert a nested SQL language query to a dataflow tree diagram with nodes of expessional operators and relational operators; and a subquery transformer provided to transform the dataflow tree diagram to a transformed dataflow tree diagram in which no expression node has a relational node as its child, the transformation proceeding in accordance with at least one rule selected from the group of SQL transformation rules NR3, NR4, NR5, NR6, and NR7.
Claim 10
Independent Claim
85 words
An apparatus for the elimination of SQL subqueries, comprising: a parser provided to convert a nested SQL language query to a dataflow tree diagram with nodes of expessional operators and relational operators; and a subquery transformer provided to transform the dataflow tree diagram to a transformed dataflow tree diagram in which no expression node has a relational node as its child, the transformation proceeding in accordance with at least one rule selected from the group of SQL transformation rules NR3, NR4, NR5, NR6, and NR7.
Claim 11
Dependent Claim
15 words
The apparatus of claim 5, the group of transformation rules further including NR1 and NR2.
Claim 12
Dependent Claim
17 words
The apparatus of claim 5, wherein the subquery transformer is located in a parser of the apparatus.
Claim 13
Dependent Claim
17 words
The apparatus of claim 5, wherein the subquery transformer is located in a binder of the apparatus.
Claim 14
Dependent Claim
17 words
The apparatus of claim 5, wherein the subquery transformer is located in a normalizer of the apparatus.
Claim 15
Dependent Claim
17 words
The apparatus of claim 5, wherein the subquery transformer is located in a optimizer of the apparatus.
Claim 16
Dependent Claim
35 words
The apparatus of claim 5, wherein the parser converts a second SQL language query to a second dataflow tree diagram, and further comprising a binder provided to link the first and second dataflow tree diagrams.
Claim 17
Dependent Claim
12 words
The method of claim 1, wherein the transforming step transforms multiple subqueries.
Claim 18
Dependent Claim
13 words
The method of claim 1, wherein the transforming step transforms recursively nested subqueries.
Claim 19
Dependent Claim
32 words
The method of claim 1, wherein the transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate subqueries in an "all" predicate.
Claim 20
Dependent Claim
41 words
The method of claim 1, wherein the transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate subqueries in a predicate that is capable of being transformed into an "all" predicate.
Claim 21
Dependent Claim
33 words
The method of claim 1, wherein the transforming step includes the step of transforming said SQL language data flow tree in accordance with a normalization rule to eliminate subqueries in an "any" predicate.
Claim 22
Dependent Claim
42 words
The method of claim 1, wherein the transforming step includes the step of transforming said SQL language data flow tree in accordance with a normalization rule to eliminate subqueries in a predicate that is capable of being transformed into an "any" predicate.
Claim 23
Dependent Claim
33 words
The method of claim 1, wherein the transforming step includes the step of transforming said SQL language data flow tree in accordance with a normalization rule to eliminate subqueries in a "one-row" predicate.
Claim 24
Dependent Claim
42 words
The method of claim 1, wherein the transforming step includes the step of transforming said SQL language data flow tree in accordance with a normalization rule to eliminate subqueries in a predicate that is capable of being transformed into a "one-row" predicate.
Claim 25
Dependent Claim
32 words
The method of claim 1, wherein said transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate subqueries in a "select" node.
Claim 26
Dependent Claim
35 words
The method of claim 1, wherein said transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate "one -- row" expressions inside a "select" node.
Claim 27
Dependent Claim
32 words
The method of claim 1, wherein said transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate subqueries inside a "project" node.
Claim 28
Dependent Claim
32 words
The method of claim 1, wherein said transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate subqueries inside a "sort" node.
Claim 29
Dependent Claim
32 words
The method of claim 1, wherein said transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate subqueries inside a "groupby" node.
Claim 30
Dependent Claim
33 words
The method of claim 1, wherein said transforming step includes the step of transforming said SQL language dataflow tree in accordance with a normalization rule to eliminate a subquery inside an input node.
References Cited (Backward Citations)
US5367675A
Cited
US5412804A
Cited
USPTO Annuity & Maintenance Windows
| Annuity Stage | Window Opens | Payment Deadline | Surcharge Period Until | Estimated Fee | Status |
|---|---|---|---|---|---|
| 3.5 Years (Year 4) | 2001-03-02 | 2001-09-01 | 2002-03-03 | $2000.00 | Window Closed / Lapsed |
| 7.5 Years (Year 8) | 2005-03-02 | 2005-09-01 | 2006-03-03 | $3760.00 | Window Closed / Lapsed |
| 11.5 Years (Year 12) | 2009-03-02 | 2009-09-01 | 2010-03-03 | $7700.00 | Window Closed / Lapsed |