EFFICIENT QUERY PROCESSING ON ORDERED VIEWS

• US 20130238637A1
• Filed: 03/06/2012
• Published: 09/12/2013
• Est. Priority Date: 03/06/2012
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Murthy, Raghupathi K., Gupte, Ajaykumar B., Zuzarte, Joaquim A.
• Granted Patent: US 9,471,630 B2
• Time in Patent Office: Days
• US Class Current: 707/752
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination

Abstract

The efficient query processing on ordered views includes: obtaining a view definition for a view for database data; obtaining a query to be executed on the database data; identifying any columns in the view that are unnecessary for the query; after processing any predicates of the query, identifying any sorts in the view definition that are unnecessary or redundant in the query; rewriting the query to eliminate the unnecessary columns and unnecessary and redundant sorts; and executing the rewritten query without materializing the view. Thus, the query is rewritten in a way that avoids materializing the view and eliminates duplicate or unnecessary sorts. This reduces the number of rows to be sorted and eliminates unnecessary columns from materialization. These reductions lead to resource savings, including memory, temporary table space required for sorting, and CPU resources.

Claims

1. A method for efficient query processing on ordered views by a computing device comprising a processor, comprising:

obtaining a view definition for a view for database data, the view definition comprising one or more view sorts;

obtaining a query to be executed on the database data;

identifying any columns in the view that are non-projected for the query;

after processing any predicates of the query, identifying at least one view sort that is based on at least one non-projected view column and affects which rows of the database data will be in the view; and

rewriting the query to eliminate the non-projected view columns and to apply the at least one view sort.

2. The method of claim 1, further comprising:

executing the rewritten query without materializing the view.

3. The method of claim 1, further comprising:

determining that another view sort is not used in the query and does not affect query results; and

in response to determining that the other view sort is not used in the query and does not affect the query results, not applying the other view sort to the query.

4. The method of claim 1, further comprising:

determining that another view sort is used in the query or affects query results;

in response to determining that the other view sort is used in the query or does affect the query results, determining whether the query comprises one or more sorts; and

in response to determining that the query does not comprise any sorts, applying the other view sort to the query.

5. (canceled)

7. A method for efficient query processing on ordered views by a computing device comprising a processor, comprising:

obtaining a view definition for a view for database data, the view definition comprising one or more view sorts;

obtaining a query to be executed on the database data, the query comprising one or more query sorts;

identifying any columns in the view that are non-projected for the query;

after processing any predicates of the query, determining that at least one view sort is based on at least one projected view column;

determining whether the at least one view sort is redundant to any query sort;

in response to determining that the at least one view sort is based on the at least one projected view column and that the at least one view sort is not redundant to any query sort, building a composite sort from the at least one view sort and the one or more query sorts; and

rewriting the query to eliminate the non-projected view columns and to apply the composite sort to the query.

6. The method of claim 7, further comprising:

in response to determining that the at least one view sort is redundant to one or more of the query sorts, not applying the at least one view sort to the query.

8. A computer program product for efficient query processing on ordered views, the computer program product comprising:

a computer readable memory device having computer readable program code embodied therewith, the computer readable program code configured to;

obtain a view definition for a view for database data, the view definition comprising one or more view sorts;

obtain a query to be executed on the database data;

identify any columns in the view that are non-projected for the query;

after processing any predicates of the query, identify at least one view sort that is based on at least one non-projected view column and affects which rows of the database data will be in the view; and

rewrite the query to eliminate the non-projected view columns and to apply the at least one view sort.

9. The computer program product of claim 8, wherein the computer readable program code is further configured to:

execute the rewritten query without materializing the view.

10. The computer program product of claim 8, wherein the computer readable program code is further configured to:

determine that another view sort is not used in the query and does not affect query results; and

in response to determining that the other view sort is not used in the query and does not affect the query results, not apply the other view sort to the query.

11. The computer program product of claim 8, wherein the computer readable program code is further configured to:

determine that another view sort is used in the query or affects query results;

in response to determining that the other view sort is used in the query or does affect the query results, determine whether the query comprises one or more sorts; and

in response to determining that the query does not comprise any sorts, apply the other view sort to the query.

12. (canceled)

14. A computer program product for efficient query processing on ordered views, the computer program product comprising:

a computer readable memory device having computer readable program code embodied therewith, the computer readable program code configured to;

obtain a view definition for a view for database data, the view definition comprising one or more view sorts;

obtain a query to be executed on the database data, the query comprising one or more query sorts;

identify any columns in the view that are non-projected for the query;

after processing any predicates of the query, determine that at least one view sort is based on at least one projected view column;

determine whether the at least one view sort is redundant to any query sort;

in response to determining that the at least one view sort is based on the at least one projected view column and that the at least one view sort is not redundant to any query sort, build a composite sort from the at least one view sort and the one or more query sorts; and

rewrite the query to eliminate the non-projected view columns and to apply the composite sort to the query.

13. The computer program product of claim 14, wherein the computer readable program code is further configured to:

in response to determining that the at least one view sort is redundant to one or more of the query sorts, not apply the at least one view sort to the query.

15. A system, comprising:

a processor; and

a computer readable memory device having computer readable program code embodied therewith executable by the processor, the computer readable program code configured to;

obtain a view definition for a view for database data, the view definition comprising one or more view sorts;

obtain a query to be executed on the database data;

identify any columns in the view that are non-projected for the query;

after processing any predicates of the query, identify at least one view sort that is based on at least one non-projected view column and affects which rows of the database data will be in the view; and

rewrite the query to eliminate the non-projected view columns and to apply the at least one view sort.

16. The system of claim 15, wherein the computer readable program code is further configured to:

execute the rewritten query without materializing the view.

17. The system of claim 15, wherein the computer readable program code is further configured to:

determine that another view sort is not used in the query and does not affect query results; and

in response to determining that the other view sort is not used in the query and does not affect the query results, not apply the other view sort to the query.

18. The system of claim 15, wherein the computer readable program code is further configured to:

determine that another view sort is used in the query or affects query results;

in response to determining that the other view sort is used in the query or does affect the query results, determine whether the query comprises one or more sorts; and

in response to determining that the query does not comprise any sorts, apply the other view sort to the query.

19. (canceled)

21. A system, comprising:

a processor; and

a computer readable memory device having computer readable program code embodied therewith executable by the processor, the computer readable program code configured to;

obtain a view definition for a view for database data, the view definition comprising one or more view sorts;

obtain a query to be executed on the database data, the query comprising one or more query sorts;

identify any columns in the view that are non-projected for the query;

after processing any predicates of the query, determine that at least one view sort is based on at least one projected view column;

determine whether the at least one view sort is redundant to any query sort;

in response to determining that the at least one view sort is based on the at least one projected view column and that the at least one view sort is not redundant to any query sort, build a composite sort from the at least one view sort and the one or more query sorts; and

rewrite the query to eliminate the non-projected view columns and to apply the composite sort to the query.

20. The system of claim 21, wherein the computer readable program code is further configured to:

in response to determining that the at least one view sort is redundant to one or more of the query sorts, not apply the at least one view sort to the query.