Query optimization using materialized views in database management systems
AssigneeiAnywhere Solutions, Inc.
InventorAnisoara Nica
Life of this patent
Estimating the term…
Abstract
Systems, methods and computer program products for optimization of query evaluation using materialized views in a database management system (DBMS) are described herein. During a pre-optimization phase, building blocks are generated that can be used to generate valid partial execution plans, where at least one of the building blocks can be a materialized view. During an enumeration phase, a plurality of execution plans for the query are generated using the building blocks. Some of the execution plans may include materialized views.
Claims17
A method of optimizing evaluation of queries in a database management system, comprising:
- determining building blocks that can be used to generate valid partial execution plans, wherein at least one of said building blocks is a materialized view;
- and
- generating a plurality of execution plans for a query using said building blocks, wherein at least one of said execution plans includes a materialized view,
- wherein said generating step comprises:
- adding a table to an access plan when said materialized view using said table is not already represented in said access plan;
- and
- adding said materialized view to said access plan when said access plan does not already contain any table referenced by said materialized view,
- wherein said determining, said generating and said adding steps are implemented on one or more processors.
A query optimizer executing in a database management system that optimizes evaluation of queries, comprising:
- a pre-optimization phase module that determines building blocks that can be used to generate valid partial execution plans, wherein at least one of said building blocks is a materialized view;
- and
- an enumeration phase module that generates a plurality of execution plans for a query using said building blocks, wherein at least one of said execution plans includes a materialized view,
- wherein said enumeration phase module comprises:
- a first adding module for adding a table to an access plan when said materialized view using said table is not already represented in said access plan;
- and
- a second adding module for adding said materialized view to said access plan when said access plan does not already contain any table referenced by said materialized view,
- wherein said pre-optimization phase module, said enumeration phase module, said first adding module and said second adding module are implemented on one or more processors.
A computer program product comprising a tangible computer useable medium having computer program logic stored thereon for enabling a processor to optimize evaluation of database queries, said computer program logic comprising:
- pre-optimization means for enabling said processor to determine building blocks that can be used to generate valid partial execution plans, wherein at least one of said building blocks is a materialized view;
- and
- enumeration means for enabling said processor to generate a plurality of execution plans for a query using said building blocks, wherein at least one of said execution plans includes a materialized view,
- wherein said enumeration means comprises:
- first adding means for enabling said processor to add a table to an access plan when said materialized view using said table is not already represented in said access plan;
- and
- second adding means for enabling said processor to add said materialized view to said access plan when said access plan does not already contain any table referenced by said materialized view.
A computer program product comprising a tangible computer useable medium having computer program logic stored thereon for enabling a processor to optimize evaluation of database queries, said computer program logic comprising:
- pre-optimization means for enabling said processor to determine building blocks that can be used to generate valid partial execution plans, wherein at least one of said building blocks is a materialized view;
- and
- enumeration means for enabling said processor to generate a plurality of execution plans for a query using said building blocks, wherein at least one of said execution plans includes a materialized view,
- wherein said pre-optimization means comprises:
- means for enabling said processor to determine that a candidate materialized view is a building block when (a) predicates of said candidate materialized view are less restrictive than predicates of said query, and (b) tables of said candidate materialized view are a subset of tables of said query;
- wherein said computer program logic further comprises:
- means for enabling said processor to generate a first dictionary that records predicates associated with said candidate materialized view;
- means for enabling said processor to generate a second dictionary that records predicates associated with each column of a given base table referenced by said query;
- and
- means for enabling said processor to reference said first and second dictionaries to determine whether predicates of said candidate materialized view are less restrictive than predicates of said query.
A computer program product comprising a tangible computer useable medium having computer program logic stored thereon for enabling a processor to optimize evaluation of database queries, said computer program logic comprising:
- pre-optimization means for enabling said processor to determine building blocks that can be used to generate valid partial execution plans, wherein at least one of said building blocks is a materialized view;
- and
- enumeration means for enabling said processor to generate a plurality of execution plans for a query using said building blocks, wherein at least one of said execution plans includes a materialized view,
- wherein said materialized view or a table is removed from further consideration for addition to said access plan by inserting a collapse node into said access plan.
Dependencies are inferred from the claim text.