Query optimization by transparently altering properties of relational tables using materialized views
DCFirst Claim
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 the steps of:
- (a) accepting the query into the computer system;
(b) determining whether there exists one or more materialized views for one or more tables referenced in the query, wherein the materialized view has different partitioning or replication properties than the tables referenced in the query;
(c) analyzing whether at least a portion of the query can be evaluated using one or more of the materialized views in a local fashion, so that no data movement is required for the evaluation;
(d) rewriting the query to use one or more materialized views rather than an original table or tables referenced in the query; and
(e) executing the rewritten query using one or more materialized views.
4 Assignments
Litigations
0 Petitions
Accused Products
Abstract
A method, apparatus, and article of manufacture for optimizing database queries using a materialized view for a table referenced in the query, wherein the materialized view has different properties than the referenced table. The materialized view may be replicated across multiple processors of the computer system, so that some or all of the query can be executed in a local fashion wherein there is no data movement required to perform the operations. The materialized view also may be partitioned across multiple processors of the computer system using a different partitioning key than the referenced table. The materialized view may be a vertical and/or horizontal subset of the table, so that only selected columns and/or tuples from the table are present therein. Columns may be added to the materialized view to contain pre-computed results of complex expressions, and indices may be created on the columns of the materialized view.
-
Citations
72 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 the steps of:
-
(a) accepting the query into the computer system;
(b) determining whether there exists one or more materialized views for one or more tables referenced in the query, wherein the materialized view has different partitioning or replication properties than the tables referenced in the query;
(c) analyzing whether at least a portion of the query can be evaluated using one or more of the materialized views in a local fashion, so that no data movement is required for the evaluation;
(d) rewriting the query to use one or more materialized views rather than an original table or tables referenced in the query; and
(e) executing the rewritten query using one or more materialized views. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
-
-
13. A method of optimizing a query in a computer system having a plurality of processors, the query being performed by the computer system to retrieve data from a database stored across the processors of the computer system, the method comprising the steps of:
-
(a) materializing a view for a table of the database, wherein the materialized view has different properties than the table;
(b) replicating the materialized view across the processors of the computer system;
(c) analyzing a query into the computer system to determine whether the materialized view can be substituted for a table referenced in the query, so that at least a portion of the query can be evaluated in a local fashion;
(d) rewriting the query to use the replicated materialized view rather than the table referenced in the query; and
(e) executing the query using the replicated materialized view.- View Dependent Claims (14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24)
-
-
25. An apparatus for optimizing a query, comprising:
-
(a) a computer system having a data storage device coupled thereto for storing a database, the query being performed by the computer to retrieve data from the database; and
(b) means, performed by the computer system, for accepting the query into the computer system;
for determining whether there exists one or more materialized views for one or more tables referenced in the query, wherein the materialized view has different partitioning or replication properties than the tables referenced in the query;
for analyzing whether at least a portion of the query can be evaluated using one or more of the materialized views in a local fashion, so that no data movement is required for the evaluation, for rewriting the query to use one or more materialized views rather than an original table or tables referenced in the query; and
for executing the rewritten query using one or more materialized views.- View Dependent Claims (26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36)
-
-
37. An apparatus for optimizing a query, comprising:
-
(a) a computer having a plurality of processors, the query being performed by the computer to retrieve data from a database stored across the processors of the computer system; and
(b) means, performed by the computer, for materializing a view for a table of the database, wherein the materialized view has different properties than the table;
for replicating the materialized view across the processors of the computer system;
for analyzing a query into the computer system to determine whether the materialized view can be substituted for a table referenced in the query, so that at least a portion of the query can be evaluated in a local fashion;
for rewriting the query to use the replicated materialized view rather than the table referenced in the query; and
for executing the query using the replicated materialized view.- View Dependent Claims (38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48)
-
-
49. An article of manufacture embodying logic for performing method steps for optimizing a query, the query being performed by a computer system to retrieve data from a database stored in a data storage device coupled to the computer system, the method comprising the steps of:
-
(a) accepting the query into the computer system;
(b) determining whether there exists one or more materialized views for one or more tables referenced in the query, wherein the materialized view has different partitioning or replication properties than the tables referenced in the query;
(c) analyzing whether at least a portion of the query can be evaluated using one or more of the materialized views in a local fashion, so that no data movement is required for the evaluation;
(d) rewriting the query to use one or more materialized views rather than an original table or tables referenced in the query; and
(e) executing the rewritten query using one or more materialized views. - View Dependent Claims (50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60)
-
-
61. An article of manufacture embodying logic for performing method steps for optimizing a query, the query being performed by a computer system having a plurality of processors to retrieve data from a database stored across the processors of the computer system, the method comprising the steps of:
-
(a) materializing a view for a table of the database, wherein the materialized view has different properties than the table;
(b) replicating the materialized view across the processors of the computer system;
(c) analyzing a query into the computer system to determine whether the materialized view can be substituted for a table referenced in the query, so that at least a portion of the query can be evaluated in a local fashion;
(d) rewriting the query to use the replicated materialized view rather than the table referenced in the query; and
(e) executing the query using the replicated materialized view. - View Dependent Claims (62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72)
-
Specification