Optimization of joined table expressions by extended access path selection

• US 6,421,663 B1
• Filed: 06/14/1999
• Issued: 07/16/2002
• Est. Priority Date: 06/14/1999
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lin, Fen-Ling, Wang, Yun, Chen, Yao-Ching Stephen, Mukai, Jerry, Tie, Hong
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/332,600
• Time in Patent Office: 1,128 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24526 Internal representations for queries
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A method, apparatus, and article of manufacture for analyzing a query and extending an access path selection for the query, in order to optimize joined table expressions and complex join operations in the query. The extension of the access path selection occurs in two phases: (1) an access path selection router analyzes and divides the query into portions, so that the overall access path selection is divided into smaller access path selections for processing by a join enumerator; and (2) the join enumerator is provided to handle joined table expressions and complex join operators, wherein the join enumerator can be continuously enhanced as more powerful query optimization techniques become available.

Claims

1. A method of optimizing a query in a relational database management system, comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the access path selection router divides an overall access path selection into smaller access path selections that are processed separately by the join enumerator.

2. A method of optimizing a query in a relational database management system, comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the access path selection router understands what optimization functions can be performed by the join enumerator.

3. A method of optimizing a query in a relational database management system, comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the join enumerator optimizes joined table expressions and join operators within each of the separate portions of the query.

4. A method of optimizing a query in a relational database management system, comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the join enumerator introduces a partial ordering relationship among tables to model left/right outer join order preference.

5. A method of optimizing a query in a relational database management system, comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the join enumerator introduces a cluster relationship among table references.

6. An apparatus for optimizing a query in a relational database management system, comprising:

(a) an access path selection router for dividing the query into separate portions; and

(b) a join enumerator for optimizing access path selection in each of the separate portions individually;

(c) wherein the access path selection router divides an overall access path selection into smaller access path selections that are processed separately by the join enumerator.

7. An apparatus for optimizing a query in a relational database management system, comprising:

(a) an access path selection router for dividing the query into separate portions; and

(b) a join enumerator for optimizing access path selection in each of the separate portions individually;

(c) wherein the access path selection router understands what optimizing functions can be performed by the join enumerator.

8. An apparatus for optimizing a query in a relational database management system, comprising:

(a) an access path selection router for dividing the query into separate portions; and

(b) a join enumerator for optimizing access path selection in each of the separate portions individually;

(c) wherein the join enumerator optimizes joined table expressions and join operators within each of the separate portions of the query.

9. An apparatus for optimizing a query in a relational database management system, comprising:

(a) an access path selection router for dividing the query into separate portions; and

(b) a join enumerator for optimizing access path selection each of the separate portions individually;

(c) wherein the join enumerator introduces a partial ordering relationship among tables to model left/right outer join order preference.

10. An apparatus for optimizing a query in relational database management system, comprising:

(a) an access path selection router for dividing the query into separate portions, and

(b) a join enumerator for optimizing access path selection in each of the separate portions individually;

(c) wherein the join enumerator introduces a cluster relationship among table references.

11. An article of manufacture embodying logic for performing a method of optimizing a query in a relational database management system, the method comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the access path selection router divides an overall access path selection into smaller access path selection,s that are processed separately by the join enumerator.

12. An article of manufacture embodying logic for performing a method of optimizing a query in a relational database management system, the method comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the access path selection router understands what optimization functions can be performed by the join enumerator.

13. An article of manufacture embodying logic for performing, a method of optimizing a query in a relational database management system, the method comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the join enumerator optimizes joined table expressions and join operators within each of the separate portions of the query.

14. An article of manufacture embodying logic for performing, a method of optimizing a query in a relational database management system, the method comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the join enumerator introduces a partial ordering relationship among;

tables to model left/right outer join order preference.

15. An article of manufacture embodying logic for performing a method of optimizing a query in a relational database management system, the method comprising:

(a) dividing the query into separate portions in an access path selection router; and

(b) providing each of the separate portions to a join enumerator, so that access path selection for each of the separate portions can be optimized individually;

(c) wherein the join enumerator introduces a cluster relationship among table references.