Concurrency control for transactions that update base tables...

Details Concurrency control for transactions that update base tables... Concurrency control for transactions that update base tables...

1999-05-14

2002-03-05

Choules, Jack (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000

Reexamination Certificate

active

06353828

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates to database systems and more particularly to concurrency control for maintenance of materialized views.
BACKGROUND OF THE INVENTION
Relational databases store information in collections of tables, in which each table is organized into rows and columns. A popular application of relational database technology is data warehousing, in which an organization's data repository is designed to support the decision-making process for the organization. Many data warehouses are characterized by a schema having a very large table called a “fact table” and many smaller lookup tables called “dimension tables.” A fact table contains information collected for analysis and dimension tables contain information about the attributes of the data in the large fact table.
FIG. 5
illustrates an exemplary database containing two tables, a product table
500
and a sales table
510
, useful for recording and organizing information about a company's sales operation. The columns of the product table
500
hold attributes for the different products sold by the company, including a product number “PRODNO”
502
, and a product name “PNAME”
404
. Information about each product is stored in a row. For example, the first row is an entry for product
11
, which is peanuts. The sales table
410
holds information in columns for each sale of products made by the company. Such information may include, for example, the number of tons
412
of product in the sale, and the product number
414
of the product that was sold. In this example, the sales table
510
may be considered to be a fact table because the sales information is useful in analysis of profitability, and the product table
400
is an example of a dimension table because it stores information about the product number attribute
414
.
A database user retrieves information from the tables of a relational database by entering input that is converted to queries by a database application, which submits the queries to a database server. In response to receiving a query, the database server accesses the tables specified in the query to determine which information within the tables satisfies the query. The information that satisfies the queries is then retrieved by the database server and transmitted to the database application and ultimately presented to the user. Database servers are also capable of combining or “aggregating” information contained in different tables in response to a query. For example, one query for the exemplary database is to list, for each sale, the tonnage sold from the sales table
410
and the corresponding name of the product sold from the product table
400
. This query would be useful for users who don't remember the meaning of the product numbers.
For any given database application, the queries must conform to the rules of a particular query language. Most query languages provide users with a variety of ways to specify information to be retrieved. For example, in the Structured Query Language (SQL), the following query requests the retrieval of a list, for each sale, of the tonnage sold and the name of the product sold:
STATEMENT 1
SELECT TONS, PNAME
FROM PRODUCT, SALES
WHERE PRODUCT.PRODNO=SALES.PRODNO;
This query performs a join operation on the product table
500
and the sales table
510
. A join operation combines rows from two or more relational database objects, such as tables, views, or snapshots. To process a join operation, the database server combines pairs of rows that satisfy the join conditions and the other predicates. A join is performed whenever multiple tables appear in the FROM clause of query. The SELECT list of the query can reference any of the columns from any of the base objects listed in the FROM clause. Most join queries contain a WHERE clause that contains a predicate that compares two columns, each from a different joined object. Such predicates are referred to join conditions.
For various reasons, it is desirable to define views that present results of queries. For example, views are often used to provide security by hiding sensitive information or simplify commands for a user by hiding the complexity of the database. A view is a logical table, and as logical tables, views can be queried just as if they were tables. The data that views actually present, however, is extracted or derived from other database objects, which may in fact be tables, other views, or snapshots. Columns and tables that are mapped to a view are referred to herein as base columns and base tables of the view, respectively.
A view is defined by metadata referred to as a view definition, which is typically in the form of a database query. For example, to create a view “RESULTS”, illustrated a view
520
in
FIG. 5
, for presenting the results of the query of STATEMENT 1, the following STATEMENT 2 may be issued to define the view:
STATEMENT 2
CREATE VIEW RESULTS AS
SELECT TONS, PNAME
FROM PRODUCT, SALES
WHERE PRODUCT.PRODNO=SALES.PRODNO;
The data presented by conventional views is gathered and derived on-the-fly from the base tables in response to queries that access the views. This data is not persistently stored after the query accessing the view has been processed. Since the data provided by conventional views is gathered from the base tables at the time the views are accessed, the data from the views will reflect the current state of the base tables. The overhead, however, associated with gathering the data from the base tables for a view every time the view is accessed may be prohibitive, especially if the defining query of the view is expensive to compute.
A materialized view, on the other hand, is a view for which a copy of the view data is stored separately from the base tables of the materialized view. Since the view data is persistently stored, the view data is not required to be regenerated every time a query accesses the view, eliminating overhead associated gathering and deriving the view. In order to provide up-to-date view data, however, materialized views must be maintained to reflect the current state of the base tables. When the base tables of a materialized view are modified, corresponding changes are made to the materialized view. Using a materialized view can lead to a cost savings compared with use of a conventional view when the materialized view presents a set of data that is infrequently changed but frequently accessed, especially when the defining query is costly to calculate.
In general, there are two approaches to causing a materialized view to reflect changes made to its base tables. One approach, referred to as a total refresh, involves discarding the current materialized view data and reissuing the defining query to regenerate the entire materialized view based on the current state of the base tables. Total refresh, thus, incurs a significant performance penalty in regenerating the view, especially for large base tables, and reduces the relative performance benefit of using the materialized view.
The other approach for maintaining a materialized view is referred to herein as incremental maintenance. With incremental maintenance, the entire materialized view is not regenerated every time a base table is changed. Instead, the database server determines what changes, if any, must be made to the materialized view data to reflect the changes made to the base tables. Incremental maintenance significantly reduces the overhead associated with maintaining a materialized view when, for example, changes to the base table only require the insertion or deletion of a single row within the materialized view.
When performing a incremental maintenance on a materialized view that is defined by a join on a plurality of base tables, it is important to control the order of operations performed by concurrent transactions to avoid the “missing updates problem.” A transaction is a logical unit of work that comprises one or more databese language statements, terminated by a “commit” or “abort” operation. Concurrent transactions are received and p

Affiliated with

Also associated with

Rating

Concurrency control for transactions that update base tables... does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Concurrency control for transactions that update base tables..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Concurrency control for transactions that update base tables... will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2840768