View composition in a data base management system

• US 5,276,870 A
• Filed: 10/09/1990
• Issued: 01/04/1994
• Est. Priority Date: 12/11/1987
• Status: Expired due to Fees

• Current Assignee: Hewlett-Packard Company (HP Inc.)
• Original Assignee: Hewlett-Packard Company (HP Inc.)
• Inventors: Shan, Ming-Chien, Lyngbaek, Peter
• Primary Examiner(s): Lee, Thomas C.
• Assistant Examiner(s): Von Buhr, Maria N.
• Application Number:
  US07/595,717
• Time in Patent Office: 1,183 Days
• Field of Search: 395/600, 364/DIG. 1, 364/DIG. 2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: G06F 16/2477 Temporal data queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

In a database management system that operates according to the Structured Query Language standard, a query containing a reference to a view is processed by dynamically materializing the view through execution of the view definition. Once the view is materialized, it is treated as any other base table. To enable a query optimization plan to refer to the materialized view, a view node is introduced into the execution plan. The view node includes a subquery that results in the creation of the virtual table defined by the view. This created table is temporarily stored in a memory. The query optimizer can treat view nodes in the same manner as stored base tables, and thereby overcomes restrictions that were placed upon views by previous view decomposition approaches.

Claims

1. In a database system having data and a plurality of view definitions stored in a memory of a computer, the data arranged in base table and each view definition defining a view in terms of the base tables, a method of processing a query that reference one or more of the views, the method comprising:

classifying any view that is defined in terms of only one base table as a single-table view;

classifying any view that is not a single-table view and that is defined in terms of execution of a predetermined operation on a base table as a complex view;

classifying any view that is not a single-table view and that is not a complex view as a simple view;

selecting every view that has been referenced by the query and that has been classified as a complex view;

composing a view table according to the definition of each selected view by retrieving the stored data and manipulating the retrieved data in the computer according to said definition to provide tuples of the view table;

evaluating the query by manipulating the provided view table tuples in the computer according to the query to obtain a result; and

providing a result of the evaluation.

2. In a database system having data and a plurality of view definitions stored in a memory of a computer, the data arranged in base tables and each view definition defining a view in terms of the base tables, a method of processing a query that references one or more of the views, the method comprising:

classifying any view that is defined in terms of only one base table as a single-table view;

classifying any view that is not a single-table view and that is defined in terms of execution of a predetermined operation on a base tables as a complex view;

classifying any view that is not a single-table view and that is not a complex view as a simple view;

determining whether the computer would evaluate the query more rapidly by composing a view table according to the definition of each complex view that is referenced by the query or by not composing such view table;

selecting each complex view that is referenced by the query if an only if the result of the preceding step is that the computer would evaluate the query more rapidly by composing such view table;

composing a view table according to the definition of each selected view by retrieving the stored data and manipulating the retrieved data in the computer according to said definition to provide tuples of the view table;

evaluating the query by manipulating the provided view table tuples in the computer according to the query to obtain a result; and

providing a result of the evaluation.

3. In a database system having data and a plurality of view definitions stored in a memory of a computer, the data arranged in base tables and each view definition defining a view in terms of the base tables, a method of processing a query that references one or more of the views, the method comprising:

classifying any view that is defined in terms of only one base table as a single-table view;

classifying any view that is not a single-table view and that is defined in terms of execution of a predetermined operation on a base table as a complex view;

classifying any view that is not a single-table view and that is not a complex view as a simple view;

determining whether the computer would evaluate the query more rapidly by composing a view table according to the definition of any one of the complex views that is referenced by the query or by not composing any such view tables;

selecting all the complex views that are referenced by the query if and only if the result of the preceding step is that the computer would evaluate the query more rapidly by composing any one of such view tables;

composing a view table according to the definition of each selected view by retrieving the stored data and manipulating the retrieved data in the computer according to said definition to provide tuples of the view table;

evaluating the query by manipulating the providing view table tuples in the computer according to the query to obtain a result; and

providing a result of the evaluation.