Global optimization of correlated subqueries and exists predicates

• US 5,761,657 A
• Filed: 12/21/1995
• Issued: 06/02/1998
• Est. Priority Date: 12/21/1995
• Status: Expired due to Term

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Hoang, Chi Kim
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): WALLACE, MICHAEL J
• Application Number:
  US08/577,681
• Time in Patent Office: 894 Days
• Field of Search: 395/602, 395/603, 395/604, 395/605, 395/611, 395/614, 707/2, 707/3, 707/4, 707/5, 707/101, 707/104, 1/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method of optimizing a query and its subqueries together in a global manner regardless of the nesting levels. All joins in different nesting levels are effectively converted into joins in the same level by assigning a nesting level attribute to relations, assigning properties to the predicate, and assigning nesting level attributes and minimum relation set attributes to join predicate operands. The joins and their predicates are then considered as if they were contained in only the main query according to a set of rules based on the attributes of the relations and the join predicates.

Claims

1. A method of optimizing an SQL query in a computer having a memory, the SQL query being performed by the computer to retrieve data from a relational database stored in one or more electronic storage devices coupled to the computer, the method comprising the steps of:

(a) reducing the query in the memory of the computer by labeling query blocks, relations and predicate operands in the query according to their scope using an ordered set of query block numbers, and by determining minimum relation sets for predicate operands and predicate properties in the query;

(b) generating a plurality of join plans in the memory of the computer by applying a predetermined set of rules to the labels of the query blocks, relations and predicate operands to determine which relations can be joined together and which predicates can be applied to a particular pair of relations; and

(c) selecting a join plan in the memory of the computer from the plurality of join plans, wherein the selected join plan has a least performance cost associated therewith.

2. The method as set forth in claim 1 above, wherein the step of reducing further comprises the steps of:

(1) labeling each query block within the query by assigning a label thereto, wherein the label comprises an ordered set of query block numbers for all enclosing query blocks and a unique number assigned to the enclosed query block;

(2) labeling each relation within the query by assigning a label thereto, wherein the label comprises an ordered set of query block numbers for all query blocks immediately containing the relation;

(3) labeling each predicate operand within the query by assigning a label to left and right operands thereof, wherein the label comprises an ordered set of query block numbers for a query block where the left and right operands are specified;

(4) assigning a minimum relation set to the left and right operands of each predicate operand, wherein the minimum relation set comprises a set of base tables that must be present in the table list of a relation referenced by the predicate operand; and

(5) assigning an aggregate attribute to the right operand of each predicate operand that specifies an aggregate function.

3. The method as set forth in claim 1 above, wherein the step of generating comprises the step of joining a first relation to a second relation where the first relation'"'"'s label is identical to the second relation'"'"'s label.

4. The method as set forth in claim 1 above, wherein the step of generating comprises the step of joining a first relation to a second relation where the first relation'"'"'s label is subset of the second relation'"'"'s label.

5. The method as set forth in claim 1 above, wherein the step of generating comprises the step of joining a first relation to a second relation where the first relation'"'"'s label is superset of the second relation'"'"'s label.

6. The method as set forth in claim 1 above, wherein the step of generating comprises the steps of labeling a joined relation with an outer relation'"'"'s label when connecting predicate is used, and labeling a joined relation with an inner relation'"'"'s label when connecting predicate is not used.

7. An apparatus for optimizing an SQL query, comprising:

(a) a computer having a memory and one or more electronic storage devices coupled thereto, the data storage devices storing a relational database;

(b) means, performed by the computer, for accepting the SQL query into the memory of the computer, the SQL query being performed by the computer to retrieve data from a relational database stored in the electronic storage devices;

(c) means, performed by the computer, for reducing the query in the memory of the computer by labeling query blocks, relations and predicate operands in the query according to their scope using an ordered set of query block numbers, and by determining minimum relation sets for predicate operands and predicate properties in the query;

(d) means, performed by the computer, for generating a plurality of join plans in the memory of the computer by applying a predetermined set of rules to the labels of the query blocks, relations and predicate operands to determine which relations can be joined together and which predicates can be applied to a particular pair of relations; and

(e) means, performed by the computer, for selecting a join plan in the memory of the computer from the plurality of join plans, wherein the selected join plan has a least performance cost associated therewith.

8. The apparatus as set forth in claim 7 above, wherein the means for reducing further comprises:

(1) means for labeling each query block within the query by assigning a label thereto, wherein the label comprises an ordered set of query block numbers for all enclosing query blocks and a unique number assigned to the enclosed query block;

(2) means for labeling each relation within the query by assigning a label thereto, wherein the label comprises an ordered set of query block numbers for all query blocks immediately containing the relation;

(3) means for labeling each predicate operand within the query by assigning a label to left and right operands thereof, wherein the label comprises an ordered set of query block numbers for a query block where the left and right operands are specified;

(4) means for assigning a minimum relation set to the left and right operands of each predicate operand, wherein the minimum relation set comprises a set of base tables that must be present in the table list of a relation referenced by the predicate operand; and

(5) means for assigning an aggregate attribute to the right operand of each predicate operand that specifies an aggregate function.

9. The apparatus as set forth in claim 7 above, wherein the means for generating comprises means for joining a first relation to a second relation where the first relation'"'"'s label is identical to the second relation'"'"'s label.

10. The apparatus as set forth in claim 7 above, wherein the means for generating comprises means for joining a first relation to a second relation where the first relation'"'"'s label is subset of the second relation'"'"'s label.

11. The apparatus as set forth in claim 7 above, wherein the means for generating comprises means for joining a first relation to a second relation where the first relation'"'"'s label is superset of the second relation'"'"'s label.

12. The apparatus as set forth in claim 7 above, wherein the means for generating comprises means for labeling a joined relation with an outer relation'"'"'s label when connecting predicate is used, and labeling a joined relation with an inner relation'"'"'s label when connecting predicate is not used.

13. A program storage device, readable by a computer, tangibly embodying a program of instructions executable by the computer to perform method steps for executing an SQL query in a computer having a memory, the SQL query being performed by the computer to retrieve data from a relational database stored in one or more electronic storage devices coupled to the computer, the method comprising the steps of:

(a) reducing the query in the memory of the computer by labeling query blocks, relations and predicate operands in the query according to their scope using an ordered set of query block numbers, and by determining minimum relation sets for predicate operands and predicate properties in the query;

(b) generating a plurality of join plans in the memory of the computer by applying a predetermined set of rules to the labels of the query blocks, relations and predicate operands to determine which relations can be joined together and which predicates can be applied to a particular pair of relations; and

(c) selecting a join plan in the memory of the computer from the plurality of join plans, wherein the selected join plan has a least performance cost associated therewith.

14. The program storage device as set forth in claim 13 above, wherein the step of reducing further comprises the steps of:

(1) labeling each query block within the query by assigning a label thereto, wherein the label comprises an ordered set of query block numbers for all enclosing query blocks and a unique number assigned to the enclosed query block;

(2) labeling each relation within the query by assigning a label thereto, wherein the label comprises an ordered set of query block numbers for all query blocks immediately containing the relation;

(3) labeling each predicate operand within the query by assigning a label to left and right operands thereof, wherein the label comprises an ordered set of query block numbers for a query block where the left and right operands are specified;

(4) assigning a minimum relation set to the left and right operands of each predicate operand, wherein the minimum relation set comprises a set of base tables that must be present in the table list of a relation referenced by the predicate operand; and

(5) assigning an aggregate attribute to the right operand of each predicate operand that specifies an aggregate function.

15. The program storage device as set forth in claim 13 above, wherein the step of generating comprises the step of joining a first relation to a second relation where the first relation'"'"'s label is identical to the second relation'"'"'s label.

16. The program storage device as set forth in claim 13 above, wherein the step of generating comprises the step of joining a first relation to a second relation where the first relation'"'"'s label is subset of the second relation'"'"'s label.

17. The program storage device as set forth in claim 13 above, wherein the step of generating comprises the step of joining a first relation to a second relation where the first relation'"'"'s label is superset of the second relation'"'"'s label.

18. The program storage device as set forth in claim 13 above, wherein the step of generating comprises the steps of labeling a joined relation with an outer relation'"'"'s label when connecting predicate is used, and labeling a joined relation with an inner relation'"'"'s label when connecting predicate is not used.