System and method for rewriting relational database queries

• US 6,199,063 B1
• Filed: 03/27/1998
• Issued: 03/06/2001
• Est. Priority Date: 03/27/1998
• Status: Expired due to Fees

• Current Assignee: Workday Incorporated
• Original Assignee: Red Brick Systems, Inc. (International Business Machines Corporation)
• Inventors: Cole, Richard L., McKenna, William J., Johnson, Galt, Colby, Latha S., Wilhite, David G. Jr., Haslam, Edward P., Schumacher, Lee E., Jazayeri, Nasi
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/049,784
• Time in Patent Office: 1,075 Days
• Field of Search: 707/2, 707/4, 707/6, 707/3, 707/102, 707/103, 707/104
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A system and method for answering a relational database query is presented. According to an embodiment of the present invention, a database query is received. It is then determined whether that query can be rewritten in such a manner as to be able to utilize a precomputed view, such that an answer to the rewritten query is equivalent to an answer to the original query. If the query can be rewritten, it is determined whether the rewritten query can more efficiently derive the answer than the original query. The query which has the most efficiently derived answer is the query which is utilized to derive that answer.

Claims

1. A method for answering a database query, the method comprising:

providing a database query;

determining if the query should be rewritten, including a substep of determining if a first precomputed view may be utilized to rewrite the query; and

rewriting the database query by utilizing a predefined hierarchy of data, the rewriting being performed if it is determined that the query should be rewritten;

wherein the precomputed view includes at least one predicate which is not included in the query, and wherein the substep of determining if the first precomputed view may be utilized to rewrite the query further includes a substep of determining whether it is acceptable to utilize the first precomputed view which includes the at least one predicate which is not included in the query.

2. A method for answering a database query, the method comprising:

providing a database query;

determining if the query should be rewritten, including a substep of determining if a first precomputed view may be utilized to rewrite the query; and

rewriting the database query by utilizing a predefined hierarchy of data, the rewriting being performed if it is determined that the query should be rewritten;

wherein the query includes at least one predicate which is not included in the first precomputed view, and wherein the substep of determining if the first precomputed view may be utilized to rewrite the query further includes a substep of determining whether it is acceptable to utilize the first precomputed view despite the query including the at least one predicate which is not included in the first precomputed view.

3. The method of claim 2, wherein it is acceptable to utilize the first precomputed view despite the query including the at least one predicate which is not included in the first precomputed view if there is a functional dependency of data between at least one column in the first precomputed view and at least one column in the predicate that is not in the first precomputed view.

4. The method of claim 2, wherein it is acceptable to utilize the first precomputed view despite the query including the at least one predicate which is not included in the first precomputed view if there is a functional dependency of data between at least one column in the first precomputed view and each column in the predicate that is not in the first precomputed view.

5. A computer program product, tangibly stored on a computer-readable medium, for answering a database query, comprising instructions operable to cause a programmable processor to:

provide a database query;

determine if the query should be rewritten, including a substep of determining if a first precomputed view may be utilized to rewrite the query; and

rewrite the database query by utilizing a predefined hierarchy of data, the rewriting being performed if it is determined that the query should be rewritten;

wherein the precomputed view includes at least one predicate which is not included in the query, and wherein the substep of determining if the first precomputed view may be utilized to rewrite the query further includes a substep of determining whether it is acceptable to utilize the first precomputed view which includes the at least one predicate which is not included in the query.

6. A computer program product, tangibly stored on a computer-readable medium, for answering a database query, comprising instructions operable to cause a programmable processor to:

provide a database query;

determine if the query should be rewritten, including a substep of determining if a first precomputed view may be utilized to rewrite the query; and

rewrite the database query by utilizing a predefined hierarchy of data, the rewriting being performed if it is determined that the query should be rewritten;

wherein the query includes at least one predicate which is not included in the first precomputed view, and wherein the substep of determining if the first precomputed view may be utilized to rewrite the query further includes a substep of determining whether it is acceptable to utilize the first precomputed view despite the query including the at least one predicate which is not included in the first precomputed view.

7. The computer program product of claim 6, wherein it is acceptable to utilize the first precomputed view despite the query including the at least one predicate which is not included in the first precomputed view if there is a functional dependency of data between at least one column in the first precomputed view at least one column in the predicate that is not in the first precomputed view.

8. The computer program product of claim 6, wherein it is acceptable to utilized the first precomputed view despite the query including the at least one predicate which is not included in the first precomputed view if there is a functional dependency of data between at least one column in the first precomputed view and each column in the predicate that is not in the first precomputed view.