Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2001-10-02
2004-03-16
Mizrahi, Diane D. (Department: 2175)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06708179
ABSTRACT:
FIELD OF THE INVENTION
The present invention relates to DBMSs, and in particular, to maintaining materialized views.
BACKGROUND OF THE INVENTION
In a database management system (DBMS), data is stored in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In relational DBMSs, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
The present invention is not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the terms “table”, “row” and “column” shall be used herein to refer respectively to the data container, record, and field.
In a DBMS used for “data warehousing” or “decision support”, it is common for identical or closely related queries to be issued frequently. For example, a business may periodically generate reports that summarize the business facts stored in the database, such as: “What have been the best selling brands of soft drinks in each of our sales regions, during the past six months?”.
To respond to such queries, the database server typically has to perform numerous join operations because the database records contain the information that is required to respond to the queries. When a DBMS contains very large amounts of data, certain queries against the database can take an unacceptably long time to execute. The cost of executing a query may be particularly significant when the query (which takes the form of a “SELECT” statement in the SQL database language) requires join operations among a large number of database tables.
MATERIALIZED VIEWS
Among commercial users of DBMSs, it has become a common practice to store the results of often-repeated queries in database tables or some other persistent database object. By storing the results of queries, the costly join operations required to generate the results do not have to be performed every time the queries are issued. Rather, the database server responds to the queries by simply retrieving the pre-computed data.
These stored results are commonly referred to as materialized views. The contents of a materialized view is defined by metadata referred to as a view definition. The view definition contains mappings to one or more columns in the one or more tables containing the data. Columns and tables that are mapped to a materialized view are referred to herein as base columns and base tables of the materialized view, respectively. The column and the base column mapped to the column are referred to as being the same field. The data maintained in the base columns is referred to herein as base data. The data contained in a materialized view is referred to herein as materialized data.
Typically, the view definition is in the form of a database query, herein referred to as a materialized view query. The materialized view query is computed and the results are stored as the materialized view. The results can be in the form of rows, which may be rows from a single base table or a rows created by joining rows in the base table. When a “base” row (or a subset of its columns) from a base table is included in the computed results of a materialized view query that are stored, the row is referred to as being included in the materialized view. The base table whose rows are included in the materialized view are referred to as the “projected” table. Likewise, when a “base” row (or a subset of its columns) from a base table is included in the computed results of a query, the row is said to be returned by the query. Furthermore, the columns that are included in the materialized view or the computed results in the query are referred to as being projected by the materialized view or query.
Materialized views eliminate the overhead associated with gathering and deriving the data every time a query is executed. Through a process known as query rewrite, a query can be optimized to recognize and use existing materialized views that could answer the query. Typically, the query rewrite optimization is transparent to the application submitting the query. That is, the rewrite operation happens automatically and does not require the application to know about the existence of materialized views, nor that a particular materialized view has been substituted for the original query.
REFRESHING MATERIALIZED VIEWS
As new data is periodically added to the base tables of a materialized view, the materialized view needs to be updated to reflect the new base data. When a materialized view accurately reflects all of the data currently in its base tables, the materialized view is considered to be “fresh”. Otherwise, the materialized view is considered to be “stale”. A stale materialized view may be recomputed by various techniques that are collectively referred to as a “refresh”. A refresh may performed for a materialized view while modifications are concurrently being made to its base tables.
One approach to refreshing materialized views is referred to as the “total refresh” or “full refresh” approach. According to the total refresh approach, the values in materialized views are recalculated based on all of the base data every time new base data is supplied. Systems that employ the full refresh approach have the disadvantage that the re-creation process is a relatively lengthy operation due to the size and number of tables from which the materialized data is derived. For example, when ten new rows are added to a particular base table that contains a million rows, a total refresh operation would have to process all one million and ten rows of the base table to regenerate the materialized views derived using the base table.
The process of updating materialized data may be improved by performing incremental refresh, where rather than generating a new set of materialized data based on calculations that use all of the base data, the materialized data is updated based on modifications to the base data.
One type of conventional approach for incremental refresh, the concurrent refresh approach, refreshes the materialized view whenever the base tables of the materialized views are modified. A disadvantage for this approach is that it adds overhead to operations that modify the base tables. Furthermore, the overhead is incurred during periods of greatest activity for the base tables.
There are several reasons the concurrent refresh approach requires that the materialized view be refreshed whenever the base tables are changed. First, the concurrent refresh approach uses an algorithm that requires that the old values of the base table be available. Generally, these values are available if the materialized view is refreshed whenever the base table is modified. Second, the algorithm used may also require that the materialized view be refreshed based on the chronological order of changes. Refreshing the materialized view as changes are made to the base tables ensures that the materialized view is refreshed according to the chronological order of changes.
Other conventional incremental refresh approaches that can defer refresh of a materialized view may also depend on the availability of the old values of the base table and knowledge of the chronological order in which changes are made to a base table. Retaining this information can be very costly for a DBMS, or such information may simply not be available when the refresh is performed.
Furthermore, many types of materialized views cannot be incrementally refreshed by the conventional incremental refresh approaches. For example, there is no mechanism that incrementally refreshes a materialized view that defines a join operation that is based on a many-to-many relationship
Bingham Marcel K.
Hickman Palermo & Truong & Becker LLP
Mizrahi Diane D.
Oracle International Corporation
LandOfFree
Incremental refresh of materialized views for many-to-many... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Incremental refresh of materialized views for many-to-many..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Incremental refresh of materialized views for many-to-many... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3292518