Query transformation for union all view join queries using join predicates for pruning and distribution

• US 7,188,098 B2
• Filed: 09/24/2003
• Issued: 03/06/2007
• Est. Priority Date: 09/24/2003
• Status: Active Grant

• Current Assignee: Wrp IP Management, LLC
• Original Assignee: International Business Machines Corporation
• Inventors: Zhang, Guogen, Tsuji, Yumi K., Chen, Yao-Ching S., Tran, Huong T., Chieh, Ding-Wei L.
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): NGUYEN, MERILYN P
• Application Number:
  US10/669,749
• Publication Number:
  US 20050065926A1
• Time in Patent Office: 1,259 Days
• Field of Search: 707 1- 5, 707/10, 707/100, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method, apparatus, and article of manufacture for optimizing a query in a computer system, wherein the query is performed by the computer system to retrieve data from a database stored on the computer system. The optimization includes: (a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query; (b) analyzing the combined predicates; and (c) not generating the join when the analysis step indicates that the combined predicates lead to an empty result.

Claims

1. A method of optimizing a query in a computer system, the query being performed by the computer system to retrieve data from a database stored on the computer system, the method comprising:

(a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates;

(b) analyzing the combined predicates; and

(c) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result.

2. The method of claim 1, wherein a select-list of the query does not contain aggregate functions, a DISTINCT modifier, or a GROUP BY clause.

3. The method of claim 1, wherein a select-list of the query contains a DISTINCT modifier, but does not contain aggregate functions or a GROUP BY clause.

4. The method of claim 3, further comprising creating a new query block that includes a DISTINCT modifier with the select-list as a parent query block.

5. The method of claim 1, wherein a select-list of the query contains one or more aggregate functions or a GROUP BY clause.

6. The method of claim 5, further comprising creating a regrouping query block with a regrouping select-list and the GROUP BY clause, and another select-list used for distribution.

7. The method of claim 1, wherein pruning logic determines whether the combined predicates are contradictory.

8. The method of claim 7, wherein the combined predicates are contradictory if the predicates always evaluate to FALSE.

9. The method of claim 8, wherein no join is generated if the combined predicates are contradictory.

10. The method of claim 8, wherein the join is generated if the combined predicates are not contradictory.

11. A computer-implemented apparatus for optimizing a query, comprising:

(a) a computer system;

(b) means, performed by the computer system, for;

(1) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates;

(2) analyzing the combined predicates; and

(3) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result.

12. The apparatus of claim 11, wherein a select-list of the query does not contain aggregate functions, a DISTINCT modifier, or a GROUP BY clause.

13. The apparatus of claim 11, wherein a select-list of the query contains a DISTINCT modifier, but does not contain aggregate functions or a GROUP BY clause.

14. The apparatus of claim 13, further comprising logic for creating a new query block that includes a DISTINCT modifier with the select-list as a parent query block.

15. The apparatus of claim 11, wherein a select-list of the query contains one or more aggregate functions or a GROUP BY clause.

16. The apparatus of claim 15, further comprising logic for creating a regrouping query block with a regrouping select-list and the GROUP BY clause, and another select-list used for distribution.

17. The apparatus of claim 11, wherein pruning logic determines whether the combined predicates are contradictory.

18. The apparatus of claim 17, wherein the combined predicates are contradictory if the predicates always evaluate to FALSE.

19. The apparatus of claim 18, wherein no join is generated if the combined predicates are contradictory.

20. The apparatus of claim 18, wherein the join is generated if the combined predicates are not contradictory.

21. An article of manufacture comprising a computer-readable storage media embodying instructions that, when read and executed by a computer system, results in the computer system performing a method for optimizing a query, the query being performed by the computer system to retrieve data from a database stored in a data storage device coupled to the computer system, the method comprising:

(a) combining join predicates from a query with local predicates from each branch of one or more UNION ALL views referenced by the query, wherein the query joins two or more of the UNION ALL views, each UNION ALL view contains a UNION ALL of one base table with a local predicate specifying a data range in the base table, and the join predicates are on columns of the UNION ALL views that correspond to the local predicates;

(b) analyzing the combined predicates; and

(c) generating a join, except when the analyzing step indicates that the combined predicates are always FALSE and the join generates an empty result.

22. The article of claim 21, wherein a select-list of the query does not contain aggregate functions, a DISTINCT modifier, or a GROUP BY clause.

23. The article of claim 21, wherein a select-list of the query contains a DISTINCT modifier, but does not contain aggregate functions or a GROUP BY clause.

24. The article of claim 23, further comprising creating a new query block that includes a DISTINCT modifier with the select-list as a parent query block.

25. The article of claim 21, wherein a select-list of the query contains one or more aggregate functions or a GROUP BY clause.

26. The article of claim 25, further comprising creating a regrouping query block with a regrouping select-list and the GROUP BY clause, and another select-list used for distribution.

27. The article of claim 21, wherein pruning logic determines whether the combined predicates are contradictory.

28. The article of claim 27, wherein the combined predicates are contradictory if the predicates always evaluate to FALSE.

29. The article of claim 28, wherein no join is generated if the combined predicates are contradictory.

30. The article of claim 28, wherein the join is generated if the combined predicates are not contradictory.