Efficient query processing on ordered views
First Claim
1. A method for efficient query processing on ordered views by a computing device comprising a processor, comprising:
- processing a query for database data in a database system that does not support view definitions containing a sorting clause, the query processed against an ordered view without materialization of the ordered view, wherein the ordered view comprises operations directed to at least one database table, wherein a view definition that defines the ordered view comprises at least one sorting clause, wherein the view definition comprising the at least one sorting clause is not supported by the database system by;
obtaining the view definition for the ordered view;
obtaining the query to be executed on the database data, wherein the query comprises operations directed to the ordered view;
identifying any columns in the view definition 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, wherein the sorts in the view definition are defined by the at least one sorting clause, and are not supported by the database system; and
rewriting the query to eliminate the unnecessary columns, and unnecessary or redundant sorts, wherein the query is rewritten to comprise operations directed only to the at least one database table, wherein the resulting rewritten query is processed efficiently due to;
i) the elimination of the unnecessary columns;
ii) the elimination of unnecessary or redundant sorts;
iii) an effective processing of the ordered view, comprising the at least one sorting clause, without materialization; and
iv) fewer rows of data returned when the rewritten query is executed.
1 Assignment
0 Petitions
Accused Products
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.
-
Citations
20 Claims
-
1. A method for efficient query processing on ordered views by a computing device comprising a processor, comprising:
processing a query for database data in a database system that does not support view definitions containing a sorting clause, the query processed against an ordered view without materialization of the ordered view, wherein the ordered view comprises operations directed to at least one database table, wherein a view definition that defines the ordered view comprises at least one sorting clause, wherein the view definition comprising the at least one sorting clause is not supported by the database system by; obtaining the view definition for the ordered view; obtaining the query to be executed on the database data, wherein the query comprises operations directed to the ordered view; identifying any columns in the view definition 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, wherein the sorts in the view definition are defined by the at least one sorting clause, and are not supported by the database system; and rewriting the query to eliminate the unnecessary columns, and unnecessary or redundant sorts, wherein the query is rewritten to comprise operations directed only to the at least one database table, wherein the resulting rewritten query is processed efficiently due to; i) the elimination of the unnecessary columns; ii) the elimination of unnecessary or redundant sorts; iii) an effective processing of the ordered view, comprising the at least one sorting clause, without materialization; and iv) fewer rows of data returned when the rewritten query is executed. - View Dependent Claims (2, 3, 4, 5, 6, 7)
-
8. A non-transitory computer readable storage media, not including signals per se, containing a computer program product for efficient query processing on ordered views comprising:
-
the computer readable storage media having computer readable program code embodied therewith, the computer readable program code configured to; process a query for database data in a database system that does not support view definitions containing a sorting clause, the query processed against an ordered view, without materialization of the ordered view, wherein the ordered view comprises operations directed to at least one database table, wherein a view definition that defines the ordered view comprises at least one sorting clause, wherein the view definition comprising the at least one sorting clause is not supported by the database system by; obtaining the view definition for the ordered view; obtaining the query to be executed on the database data, wherein the query comprises operations directed to the ordered view; identifying any columns in the view definition 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, wherein the sorts in the view definition are defined by the at least one sorting clause, and are not supported by the database system; and rewriting the query to eliminate the unnecessary columns, and unnecessary or redundant sorts, wherein the query is rewritten to comprise operations directed only to the at least one database table, wherein the resulting rewritten query is processed efficiently due to; i) the elimination of the unnecessary columns; ii) the elimination of unnecessary or redundant sorts; iii) an effective processing of the ordered view, comprising the at least one sorting clause, without materialization; and iv) fewer rows of data returned when the rewritten query is executed. - View Dependent Claims (9, 10, 11, 12, 13, 14)
-
-
15. A system, comprising:
-
a processor; and a computer readable storage media having computer readable program code embodied therewith executable by the processor, the computer readable program code configured to; process a query for database data in a database system that does not support view definitions containing a sorting clause, the query processed against an ordered view, without materialization of the ordered view, wherein the ordered view comprises operations directed to at least one database table, wherein a view definition that defines the ordered view comprises at least one sorting clause, wherein the view definition comprising the at least one sorting clause is not supported by the database system by; obtaining the view definition for the ordered view; obtaining the query to be executed on the database data, wherein the query comprises operations directed to the ordered view; identifying any columns in the view definition 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, wherein the sorts in the view definition are defined by the at least one sorting clause, and are not supported by the database system; and rewriting the query to eliminate the unnecessary columns, and unnecessary or redundant sorts, wherein the query is rewritten to comprise operations directed only to the at least one database table, wherein the resulting rewritten query is processed efficiently due to; i) the elimination of the unnecessary columns; ii) the elimination of unnecessary or redundant sorts; iii) an effective processing of the ordered view, comprising the at least one sorting clause, without materialization; and iv) fewer rows of data returned when the rewritten query is executed. - View Dependent Claims (16, 17, 18, 19, 20)
-
Specification