System and method for optimizing queries on views defined by conditional expressions having mutually exclusive conditions

• US 20040220896A1
• Filed: 04/30/2003
• Published: 11/04/2004
• Est. Priority Date: 04/30/2003
• Status: Abandoned Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Zuzarte, Calisto Paul, Finlay, Ian Richard
• Application Number:
  US10/427,311
• Publication Number:
  US 20040220896A1
• Time in Patent Office: Days
• US Class Current: 707/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24537 of operators

Abstract

A query rewrite optimization method eliminates condition predicates of conditional expressions defining the values of a column that is referenced in the predicate of an SQL query on a view. The method evaluates the query to identify a view and a predicate referencing a column of the view. The column has values selected by a conditional expression having condition predicates defining respective mutually exclusive ranges of values. The predicate defines a first range of values. A determination is made as to whether one of the condition predicates defines a mutually exclusive range of values that comprises the first range of values. In response, the query is rewritten to eliminate the condition predicates other than the one condition predicate. The predicate is thus applied directly to the base tables without evaluating the conditional expression.

Claims

1. An information retrieval method for optimizing a query, comprising:

evaluating the query to identify a view and a predicate referencing a column of the view;

wherein the view is derived from one or more base tables;

wherein the column comprises values selected by a conditional expression having condition predicates that define respective mutually exclusive ranges of the values;

wherein the condition predicates reference one or more base columns of the base tables;

wherein the predicate define a first range of the values of the column;

determining whether one of the condition predicates defines a mutually exclusive range of the values that comprises the first range of the values; and

rewriting the query to eliminate the condition predicates other than the one of the condition predicates and to apply the predicate directly to the base tables without evaluating the conditional expression.

2. The method of claim 1 wherein the conditional expression is a CASE expression.

3. The method of claim 1 wherein the conditional expression is an IF THEN ELSE expression.

4. The method of claim 1 wherein the base tables are views.

5. The method of claim 1 wherein the view is a security view restricting users from viewing selected columns of the base tables.

6. The method of claim 1 wherein the view comprises a set of tuples.

7. A query optimizer system for use in an information retrieval system that to optimize a query, the query optimizer system comprising:

a query evaluator for the query to;

(a) identify a view and a predicate referencing a column of the view;

wherein the view is derived from one or more base tables;

wherein the column comprise values selected by a conditional expression having condition predicates defining respective mutually exclusive ranges of the values;

wherein the condition predicates reference one or more base columns of the base tables; and

wherein the predicate define a first range of the values of the column; and

(b) determine whether one of the condition predicates defines a mutually exclusive range of the values that comprises the first range of the values; and

a query rewriter for the query to rewrite the query to eliminate the condition predicates other than the one of the condition predicates and to apply the predicate directly to the base tables without evaluating the conditional expression in response to the query evaluator.

8. The query optimizer system of claim 7 wherein the conditional expression is a CASE expression.

9. The query optimizer system of claim 7 wherein the conditional expression is an IF THEN ELSE expression.

10. The query optimizer system of claim 7 wherein the base tables are views.

11. The query optimizer system of claim 7 wherein the view is a security view restricting users from viewing selected columns of the base tables.

12. The query optimizer system of claim 7 wherein the view is a set of tuples.

13. A computer program product having instruction codes for directing an information retrieval system to optimize a query, the computer program product comprising:

a first set of instruction codes for evaluating the query to identify a view and a predicate referencing a column of the view;

wherein the view is derived from one or more base tables;

wherein the column comprises values selected by a conditional expression having condition predicates that define respective mutually exclusive ranges of the values;

wherein the condition predicates reference one or more base columns of the base tables; and

wherein the predicate defines a first range of the values of the column;

a second set of instruction codes for determining whether one of the condition predicates defines a mutually exclusive range of the values that comprises the first range of the values; and

a third set of instruction codes for rewriting the query to eliminate the condition predicates other than the one of the condition predicates and to apply the predicate directly to the base tables without evaluating the conditional expression.

14. The computer program product of claim 13 wherein the conditional expression is a CASE expression.

15. The computer program product of claim 13 wherein the conditional expression is an IF THEN ELSE expression.

16. The computer program product of claim 13 wherein the base tables are views.

17. The computer program product of claim 13 wherein the view is a security view restricting users from viewing selected columns of the base tables.

18. The computer program product of claim 13 wherein the view is a set of tuples.

19. An article for directing an information retrieval system to optimize a query, comprising:

means for evaluating the query to identify a view and a predicate referencing a column of the view;

wherein the view is derived from one or more base tables;

wherein the column comprises values selected by a conditional expression having condition predicates that define respective mutually exclusive ranges of the values;

wherein the condition predicates reference one or more base columns of the base tables; and

wherein the predicate defines a first range of the values of the column;

means for determining whether one of the condition predicates defines a mutually exclusive range of the values that comprises the first range of the values; and

means for rewriting the query to eliminate the condition predicates other than the one of the condition predicates and to apply the predicate directly to the base tables without evaluating the conditional expression.

20. The article of claim 19 wherein the conditional expression is a CASE expression.

21. The article of claim 19 wherein the conditional expression is an IF THEN ELSE expression.

22. The article of claim 19 wherein the base tables are views.

23. The article of claim 19 wherein the view is a security view restricting users from viewing selected columns of the base tables.

24. The article of claim 19 wherein the view is a set of tuples.

25. An information retrieval method for optimizing a query, comprising:

evaluating the query to identify a view and a predicate that references a column of the view;

deriving the view from one or more base tables;

the column comprising values selected by a conditional expression having condition predicates that define respective mutually exclusive ranges of the values;

the condition predicates referencing one or more base columns of the base tables; and

the predicate defining a first range of the values of the column.

26. An information retrieval system for optimizing a query, comprising:

means for evaluating the query to identify a view and a predicate that reference a column of the view;

the view being derived from one or more base tables;

the column comprising values selected by a conditional expression having condition predicates that define respective mutually exclusive ranges of the values;

the condition predicates referencing one or more base columns of the base tables; and

the predicate defining a first range of the values of the column.

27. A computer program product having instruction codes for directing an information retrieval system to optimize a query, the computer program product comprising:

a first set of codes for evaluating the query. to identify a view and a predicate that reference a column of the view;

the view being derived from one or more base tables;

the column comprising values selected by a conditional expression having condition predicates that define respective mutually exclusive ranges of the values;

the condition predicates referencing one or more base columns of the base tables; and

the predicate defining a first range of the values of the column.