System for optimizing correlated SQL queries in a relational database using magic decorrelation

• US 5,548,755 A
• Filed: 02/17/1995
• Issued: 08/20/1996
• Est. Priority Date: 02/17/1995
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Seshadri, Praveen, Pirahesh, Mir H., Leung, Ting Y.
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US08/390,832
• Time in Patent Office: 550 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A system for optimizing SQL queries in a relational database management system using magic decorrelation. After representing an assembly of multiple SQL queries in computer memory using data structures configured in the query box representation ("QBR") format, queries amenable to decorrelation are identified, and FEED and ABSORB stages are applied to rewrite the QBR in decorrelated form. The FEED stage generates a set of correlation values that the subquery can use to decorrelate. The ABSORB stage decorrelates the subquery using the correlation values prepared by the FEED stage, resulting in the correlation bindings being absorbed into the subquery. The invention thereby takes full advantage of set-at-a-time, rather than tuple-at-a-time, processing.

Claims

1. A method of optimizing an SQL query that includes at least one subquery, wherein said query is executable by a computer to retrieve data from a relational database stored in electronic storage coupled to the computer, the method comprising the steps of:

representing the query in storage as a query box representation comprising a hierarchical assembly of multiple data structures corresponding to the at least one subquery, each data structure being referred to as a "box";

examining each box individually, the box under examination being referred to as the current box, to determine whether the current box has any correlated descendant box that receives correlation bindings from an original source comprising the current box or an ancestor box of the current box; and

determining whether the correlated descendant box can be decorrelated, and if so creating an optimized query by applying a magic decorrelation process to create a different source for the correlation bindings and substitute the different source for the original source, said different source being hierarchically inferior to the current box.

2. The method of claim 1, wherein the query box representation includes a child box of the current box, the child box itself having at least one child referred to as at least one grandchild box, wherein the magic decorrelation process comprises the steps of:

performing a feed process to provide the different source for the correlation bindings;

determining whether the child box is AMQ or NMQ type; and

decorrelating the correlated descendant box by performing steps comprising;

if the child box is AMQ type, performing an AMQ absorb process to decorrelate the correlated descendant box using the correlation bindings obtained from the different source, and then performing the feed process to the at least one grandchild box; and

if the child box is NMQ type, performing the feed process to the at least one grandchild box to provide the different source of the correlation bindings, and then performing an NMQ absorb process to decorrelate the correlated descendant box using correlation bindings from the different source.

3. The method of claim 2, wherein the feed process comprises the steps of:

determining whether the correlated descendant box is locally or remotely correlated;

if the correlated descendant box is remotely correlated, performing a remote feed process; and

if the descendant is locally correlated, performing a local feed process.

4. The method of claim 2, wherein the feed process comprises the steps of:

providing a decorrelated view of the correlated descendant box to the current box, said decorrelated view including a decorrelation output box interposed between the current box and the child box, said decorrelation output box including a quantifier carrying the correlation bindings, said decorrelated view also including a correlation input box to partition bindings provided by the decorrelation output box to the current box; and

modifying the correlated descendant box to receive correlation bindings from the quantifier instead of the original source.

5. The method of claim 3, wherein the correlated descendant box receives the correlation bindings from an ancestor box that receives correlation bindings from a supplementary ancestor box, and the remote feed process comprises the steps of:

applying supplementary magic to the current box to create a supplementary magic box and a magic child box;

routing output including the correlation bindings from the supplementary ancestor box to the magic child box by creating a quantifier in the magic child box ranging over the supplementary ancestor box;

in the magic child box, creating natural join predicates over common columns between the supplementary ancestor box and the supplementary magic box;

creating a decorrelation output box between the child box and the current box to provide a decorrelated view of the correlated descendant box to the current box;

directing the correlation bindings from the magic child box to the decorrelation output box by creating a quantifier in the decorrelation output box ranging over the magic child box;

creating natural join predicates in the decorrelation output box ranging over common columns between the magic child box and the child box;

providing an output of the correlation bindings at the decorrelation output box and substituting the decorrelation output box instead of the original source as the source of the correlated descendant box'"'"'s correlation bindings; and

creating a correlation input box with a correlation predicate between the original source and the correlation bindings supplied by the decorrelation output box.

6. The method of claim 3, wherein the correlated descendant box receives the correlation bindings from the current box, and the local feed process comprises steps of:

applying supplementary magic to the current box to create a supplementary magic box and a magic child box;

routing the correlation bindings from the supplementary magic box to the magic child box;

creating a decorrelation output box between the child box and the current box to provide a decorrelated view of the correlated descendant box to the current box;

directing the correlation bindings from the magic child box to the decorrelation output box by creating a quantifier in the decorrelation output box ranging over the magic child box;

creating natural join predicates in the decorrelation output box ranging over common columns between the magic child box and the child box;

providing an output of the correlation bindings at the decorrelation output box and substituting the decorrelation output box, instead of the original source, as the source of the correlated descendant box'"'"'s correlation bindings; and

creating a correlation input box with a correlation predicate between the original source and the correlation bindings supplied by the decorrelation output box.

7. The method of claim 2, wherein the child box has a parent box that comprises a decorrelation output box, said decorrelation output box having a parent box that comprises a correlation input box, said decorrelation output box including a first quantifier ranging over a magic child box associated with the child box, wherein the magic child box comprises a source of the correlation bindings, and wherein the AMQ absorb process comprises the steps of:

routing the correlation bindings from the magic child box to the child box and creating an output column in the child box to provide an output of the correlation bindings;

substituting the first quantifier in place of the decorrelation output box as the source of the correlated descendant box'"'"'s correlation bindings; and

deleting natural join predicates from the decorrelation output box and the first quantifier.

8. The method of claim 7, further comprising the steps of:

removing the decorrelation output box.

9. The method of claim 7, further comprising the steps of:

determining whether the correlation input box and the current box can be merged, and if so, performing said merging.

10. The method of claim 2, wherein the child box has a parent box that comprises a first decorrelation output box, said first decorrelation output box having a parent that comprises a first correlation input box, said first decorrelation output box including a first quantifier ranging over a magic child box associated with the child box, said first decorrelation output box also including a first natural join predicate, wherein the magic box comprises a source of the correlation bindings, and wherein the NMQ absorb process comprises the steps of:

identifying each child box of the child box, referred to as a grandchild box;

determining whether any of identified correlated grandchild boxes can be decorrelated;

performing a feed process to each grandchild box by performing steps comprising;

providing a decorrelated view of the grandchild box to the child .box, said decorrelated view including;

a decorrelation box interposed between the child box and the grandchild box, wherein said decorrelation box comprises a second decorrelation output box if the grandchild box can be decorrelated, or a decorrelation input box if the grandchild box cannot be decorrelated, wherein said decorrelation box includes a second quantifier ranging over a magic box associated with the grandchild box to carry the correlation bindings;

said decorrelated view also including a second correlation input box to partition bindings provided by the decorrelation box to the current box, said second correlation input box including a second correlation predicate whose correlation source is the decorrelation box; and

modifying the correlated descendant box to receive the correlation bindings from the second quantifier instead of the original source;

eliminating the natural join predicate of the first decorrelation output box;

eliminating the second correlation predicate;

providing an output of the correlation bindings in the second correlation input box by adding an extra column in the second correlation input box; and

providing an output of the correlation bindings in the child box by adding at least one additional column in the child box.

11. The method of claim 10, further comprising eliminating the first decorrelation output box.

12. The method of claim 11, further comprising:

determining whether the second correlation input box and any other box can be merged, and if so, performing said merging.

13. The method of claim 1, wherein the SQL query is initially received interactively from a user.

14. The method of claim 1, wherein the SQL query is initially received in the form of commands embedded in a non-SQL host language.

15. The method of claim 1, further comprising steps of executing the optimized query upon the relational database.

16. For use with a database query processing system in processing a query having a subquery therein, wherein a correlation value from the query outside the subquery is used in a computation of the subquery, thereby providing a correlation between the query and the subquery in terms of a correlation binding including the correlation value, a method for decorrelating the database query from the subquery by performing steps comprising:

determining whether decorrelation of the subquery from the query is possible, and if so, proceeding with steps comprising;

identifying join bindings of the subquery and routing the identified join bindings to the subquery; and

obtaining a set of correlation bindings for decorrelating the subquery, by generating a magic set for the subquery wherein the magic set includes both the join bindings and the obtained set of correlation bindings; and

decorrelating the subquery using the obtained correlation bindings.

17. A query optimizer for use in a computer that includes a storage device to store a relational database, a digital processing apparatus to manage the relational database, and a fast-access memory, wherein the query optimizer is operable to receive a complex SQL query executable upon the relational database and to optimize said received SQL query, said received SQL query including at least one subquery, wherein said optimizer comprises:

first means for representing the query in the fast-access memory as a query box representation comprising a hierarchical assembly of multiple data structures corresponding to the at least one subquery, each said data structure being referred to as a "box";

second means for individually examining each box, the box under examination being referred to as the current box, to identify any box that receives bindings from a correlated descendant box of the current box, wherein said correlated descendant box receives correlation bindings from an original source comprising the current box or an ancestor of the current box; and

third means for determining whether the correlated descendant box can be decorrelated, and if so, creating an optimized query by applying a magic decorrelation process to create a different source for the correlation bindings and substitute the different source for the original source, said different source being hierarchically inferior to the current box.

18. A query optimizer for use in a computer that includes a storage device to store a relational database, a digital processing apparatus to manage the relational database, a query optimizer to receive a complex SQL query intended for the relational database and optimize said SQL queries, said SQL queries being represented in fast-access memory of said digital processing apparatus as a query box representation comprising a hierarchical assembly of multiple data structures corresponding to at least one subquery of the complex SQL query, said data structures being referred to as "boxes", said query optimizer comprising:

second means for individually examining each box, the box under examination being referred to as the current box, to identify any box that receives bindings from a correlated descendant box of the current box, wherein said correlated descendant box receives correlation bindings from an original source comprising the current box or an ancestor of the current box; and

third means for determining whether the correlated descendant box can be decorrelated, and if so, creating an optimized query by applying a magic decorrelation process to create a different source for the correlation bindings and substitute the different source for the original source, said different source being hierarchically inferior to the current box.

19. A query optimizer for use in a computer that includes a storage device to store a relational database, a digital processing apparatus to manage the relational database, and a fast-access memory, wherein the query optimizer is operable to receive a complex SQL query executable upon the relational database and to optimize said received SQL query, said query including a subquery, wherein a correlation value from the query outside the subquery is used in a computation of the subquery, thereby providing a correlation between the query and the subquery in terms of a correlation binding including the correlation value, said query optimizer comprising:

means for determining whether decorrelation of the subquery from the query is possible;

means for identifying join bindings of the subquery and routing the identified join bindings to the subquery; and

means for obtaining a set of correlation bindings for decorrelating the subquery, by generating a magic set for the subquery wherein the magic set includes both the join bindings and the obtained set of correlation bindings; and

means for decorrelating the subquery using the obtained correlation bindings.

20. A digital processing apparatus programmed to perform method steps for optimizing an SQL query structured to retrieve data from a relational database stored in an electronic storage device coupled accessible by the digital processing apparatus, wherein said SQL query is represented by a query box representation comprising a hierarchical assembly of multiple data structures corresponding to at least one subquery of the SQL query, said data structures being referred to as "boxes", said method steps comprising:

representing the query in storage as a query box representation comprising a hierarchical assembly of multiple data structures corresponding to the at least one subquery, each data structure being referred to as a "box";

examining each box individually, the box under examination being referred to as the current box, to determine whether the current box receives bindings from a correlated descendant box of the current box, wherein said correlated descendant box receives correlation bindings from an original source comprising the current box or an ancestor box of the current box; and

determining whether the correlated descendant box can be decorrelated, and if so creating an optimized query by applying a magic decorrelation process to create a different source for the correlation bindings and substitute the different source for the original source, said different source being hierarchically lower than the current box.

21. The apparatus of claim 20, wherein the digital processing apparatus is further programmed to construct the query box representation representing the SQL query.

22. A digital processing apparatus programmed to perform method steps for optimizing an SQL query structured to retrieve data from a relational database stored in an electronic storage device coupled accessible by the digital processing apparatus, wherein a correlation value from the query outside the subquery is used in a computation of the subquery, thereby providing a correlation between the query and the subquery in terms of a correlation binding including the correlation value, said method steps comprising:

determining whether decorrelation of the subquery from the query is possible, and if so, proceeding with steps comprising;

identifying join bindings of the subquery and routing the identified join bindings to the subquery; and

obtaining a set of correlation bindings for decorrelating the subquery, by generating a magic set for the subquery wherein the magic set includes both the join bindings and the obtained set of correlation bindings; and

decorrelating the subquery using the obtained correlation bindings.

23. A program storage device readable by a digital processing apparatus and tangibly embodying a program of instructions executable by the digital processing apparatus to perform method steps for optimizing an SQL query that includes at least one subquery, said method steps comprising:

representing the query in storage as a query box representation comprising a hierarchical assembly of multiple data structures corresponding to the at least one subquery, each data structure being referred to as a "box";

examining each box individually, the box under examination being referred to as the current box, to determine whether the current box receives bindings from a correlated descendant box of the current box, wherein said correlated descendant box receives correlation bindings from an original source comprising the current box or an ancestor box of the current box; and

determining whether the correlated descendant box can be decorrelated, and if so creating an optimized query by applying a magic decorrelation process to create a different source for the correlation bindings and substitute the different source for the original source, said different source being hierarchically lower than the current box.

24. A program storage device readable by a digital processing apparatus and tangibly embodying a program of instructions executable by the digital processing apparatus to perform method steps for optimizing an SQL query, wherein said SQL query is represented in fast-access memory of the digital processing apparatus as a query box representation comprising a hierarchical assembly of multiple data structures corresponding to subqueries of the complex SQL query, said data structures being referred to as 37 boxes", said method steps comprising:

examining each box individually, the box under examination being referred. to as the current box, to determine whether the current box receives bindings from a correlated descendant box of the current box, wherein said correlated descendant box receives correlation bindings from an original source comprising the current box or an ancestor box of the current box; and

determining whether the correlated descendant box can be decorrelated, and if so creating an optimized query by applying a magic decorrelation process to create a different source for the correlation bindings and substitute the different source for the original source, said different source being hierarchically lower than the current box.

25. The device of claim 24, wherein the current box includes a child box and the magic decorrelation process comprises the steps of:

performing a feed process to provide the different source for the correlation bindings;

determining whether the child box is AMQ or NMQ type; and

decorrelating the correlated descendant box by performing steps comprising;

if the child box is AMQ type, performing an AMQ absorb process to decorrelate the correlated descendant box using the correlation bindings obtained from the different source, and then performing the feed process to children of the child box; and

if the child box is NMQ type, performing the feed process to children of the child box to provide the different source of the correlation bindings, and then performing an NMQ absorb process to decorrelate the correlated descendant box using correlation bindings from the different source.

26. The device of claim 25, wherein the feed process comprises the steps of:

determining whether the correlated descendant box is locally or remotely correlated;

if the correlated descendant box is remotely correlated, performing a remote feed process; and

if the descendant is locally correlated, performing a local feed process.

27. The device of claim 25, wherein the feed process comprises the steps of:

providing a decorrelated view of the correlated descendant box to the current box, said decorrelated view including a decorrelation output box interposed between the current box and the child box, said decorrelation output box including a quantifier ranging over the correlation bindings, said decorrelated view also including a correlation input box to partition bindings provided by the decorrelation output box to the current box; and

modifying the correlated descendant box to receive correlation bindings from the quantifier instead of the original source.

28. The device of claim 26, wherein the correlated descendant box receives the correlation bindings from an ancestor box that receives correlation bindings from a supplementary ancestor box, and the remote feed process comprises the steps of:

applying supplementary magic to the current box to create a supplementary magic box and a magic child box;

routing the correlation bindings from the supplementary ancestor box to the magic child box by creating a quantifier in the magic child box ranging over the supplementary ancestor box;

in the magic child box, creating natural join predicates over common columns between the supplementary ancestor box and the supplementary magic box;

creating a decorrelation output box between the child box and the current box to provide a decorrelated view of the correlated descendant box to the current box;

directing the correlation bindings from the magic child box to the decorrelation output box by creating a quantifier in the decorrelation output box ranging over the magic child box;

creating natural join predicates in the decorrelation output box ranging over common columns between the magic child box and the child box;

providing an output of the correlation bindings at the decorrelation output box and substituting the decorrelation output box instead of the original source as the source of the correlated descendant box'"'"'s correlation bindings; and

creating a correlation input box with a correlation predicate between the original source and the correlation bindings supplied by the decorrelation output box.

29. The device of claim 26, wherein the correlated descendant box receives the correlation bindings from the current box, and the local feed process comprises steps of:

applying supplementary magic to the current box to create a supplementary magic box and a magic child box;

routing the correlation bindings from the supplementary magic box to the magic child box;

creating a decorrelation output box between the child box and the current box to provide a decorrelated view of the correlated descendant box to the current box;

directing the correlation bindings from the magic child box to the decorrelation output box by creating a quantifier in the decorrelation output box ranging over the magic child box;

creating natural join predicates in the decorrelation output box ranging over common columns between the magic child box and the child box;

providing an output of the correlation bindings at the decorrelation output box and substituting the decorrelation output box, instead of the original source, as the source of the correlated descendant box'"'"'s correlation bindings; and

creating a correlation input box with a correlation predicate between the original source and the correlation bindings supplied by the decorrelation output box.

30. The device of claim 25, wherein the child box has a parent box that comprises a decorrelation output box, said decorrelation output box having a parent that comprises a correlation input box, said decorrelation output box including a first quantifier ranging over a magic child box associated with the child box, wherein the magic child box comprises a source of the correlation bindings, and wherein the AMQ absorb process comprises the steps of:

routing the correlation bindings from the magic child box to the child box and creating an output column in the child box to provide an output of the correlation bindings;

substituting the first quantifier in place of the decorrelation output box as the source of the correlated descendant box'"'"'s correlation bindings; and

deleting natural join predicates from the decorrelation output box and the first quantifier.

31. The device of claim 30, wherein the method steps further comprise the steps of:

removing the decorrelation output box.

32. The device of claim 30, wherein the method steps further comprise the steps of:

merging the correlated output box and the current box.

33. The device of claim 25, wherein the child box has a parent box that comprises a first decorrelation output box, said decorrelation output box having a parent that comprises a first correlation input box, said decorrelation output box including a first quantifier ranging over a magic child box associated with the child box, wherein the magic box comprises a source of the correlation bindings, and wherein the NMQ absorb process comprises the steps of:

identifying each correlated grandchild box of the current box;

determining whether any of identified correlated grandchild boxes can be decorrelated;

performing a feed process to each grandchild box by performing steps comprising;

providing a decorrelated view of the grandchild box to the child box, said decorrelated view including;

a decorrelation box interposed between the child box and the grandchild box, wherein said decorrelation box comprises a second decorrelation output box if the grandchild box can be decorrelated, or a decorrelation input box if the grandchild box cannot be decorrelated, wherein said decorrelation box includes a second quantifier ranging over a magic box associated with the grandchild box to carry the correlation bindings;

said decorrelated view also including a correlation input box to partition bindings provided by the decorrelation box to the current box, said correlation input box including a second correlation predicate whose correlation source is the decorrelation box; and

modifying the correlated descendant box to receive the correlation bindings from the second quantifier instead of the original source;

eliminating the first correlation predicate;

eliminating the second correlation predicate;

providing an output of the correlation bindings in the second correlation input box by adding an extra column in the second correlation input box; and

providing an output of the correlation bindings in the current box by adding at least one additional column in the current box.

34. The device of claim 33, wherein the method steps further comprise the steps of eliminating the first decorrelation output box.

35. The device of claim 33, wherein the method steps further comprise the steps of eliminating the second decorrelation output box.

36. The device of claim 34, wherein the method steps further comprise the steps of:

determining whether the first correlation input box and the current box can be merged, and if so, performing said merging.

37. The device of claim 24, wherein the SQL query is initially received interactively from a user.

38. The device of claim 24, wherein the SQL query is initially received in the form of commands embedded in a non-SQL host language.

39. The device of claim 24, wherein the method steps further comprise the steps of executing the optimized query upon the relational database.