Rewriting queries to access materialized views that group along an ordered dimension

• US 6,345,272 B1
• Filed: 07/27/1999
• Issued: 02/05/2002
• Est. Priority Date: 07/27/1999
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Witkowski, Andrew, Ziauddin, Mohamed, Bello, Randall
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/361,688
• Time in Patent Office: 924 Days
• Field of Search: 707/2, 707/4, 707/6, 707/10, 707/3, 707/5, 707/1, 707/102, 707/103, 705/26, 705/27, 706/16, 709/204, 717/2, 702/2, 702/4, 702/10
• US Class Current: 707/759
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• 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/99936 Pattern matching access
• 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

Abstract

A method and apparatus are provided for rewriting aggregate queries to access a materialized view when (1) the queries place a restriction on an ordered dimension, (2) the materialized view aggregates the information referenced in the query and groups by the same dimension, and (3) the materialized view groups the information at a coarser level of granularity than the granularity associated with the restriction contained in the aggregate queries.

Claims

1. A method for processing queries, the method comprising the steps of:

receiving an aggregate query that places a restriction on an ordered dimension;

wherein said restriction is specified at a first level of granularity for the dimension;

wherein said aggregate query does not reference a materialized view that groups results at a second level of granularity of the ordered dimension;

wherein the second level of granularity is coarser than said first level of granularity;

determining whether said materialized view satisfies each condition of a first set of conditions; and

if said materialized view satisfies each condition in said first set of conditions, then rewriting said query to produce a rewritten query that references said materialized view and that includes a rewritten restriction that restricts said ordered dimension at said second level of granularity.

2. The method of claim 1, wherein said step of rewriting includes determining whether bounds of at least one coarser granule of said coarser granules coincide to bounds of said restriction.

3. The method of claim 2, wherein the step of rewriting includes if bounds of at least one coarser granule of said coarser granules coincide to bounds of said restriction, then rewriting said query to produce a rewritten query that references said at least one coarser granule.

4. The method of claim 2, wherein the step of rewriting includes rewriting said query to produce a union between a plurality of queries that include a first query and a second query, wherein:

the first query references said materialized view and includes a restriction based on one or more of the coarser granules, and

a second aggregate query that reflects data requested by said aggregate query but not data requested by said first query.

5. The method of claim 4, wherein the step of rewriting said query to produce a union between a plurality of queries includes producing said second aggregate query as an aggregate query that places a restriction on said ordered dimension, said restriction referencing at least one granule at said first level of granularity.

6. The method of claim 1, wherein ordered dimension metadata defines said ordered dimension;

wherein the step of determining whether said materialized view satisfies each condition of a first set of conditions includes examining said ordered dimension metadata to determine whether said materialized view satisfies a condition that said materialized view groups results by coarser granules for the ordered dimension; and

wherein the step of rewriting is based on said ordered dimension metadata.

7. The method of claim 1, wherein the step of receiving an aggregate query includes receiving an aggregate query that places a restriction that limits to a set of granules defined by one or more dimension tables.

8. The method of claim 1, wherein the step of receiving an aggregate query includes receiving an aggregate query that places a restriction on an ordered time dimension;

and

wherein the step of determining whether said materialized view satisfies each condition of a first set of conditions includes determining whether said materialized view satisfies a condition that said materialized view groups results by coarser granules for the ordered time dimension.

9. The method of claim 1,

wherein the method further includes the step of determining that a second materialized view satisfies a second set of conditions;

and

wherein the first set of conditions includes that said second level of granularity by which said first materialized view groups results is coarser then the level of granularity of the ordered dimension at which said materialized view groups results.

10. A computer-readable medium carrying one or more sequences of one or more instructions for processing queries, the one or more sequences of one or more instructions including instruction which, when executed by one or more processors, cause the one or more processors to perform the steps of:

receiving an aggregate query that places a restriction on an ordered dimension;

wherein said restriction is specified at a first level of granularity for the dimension;

wherein said aggregate query does not reference a materialized view that groups results at a second level of granularity of the ordered dimension;

wherein the second level of granularity is coarser than said first level of granularity;

determining whether said materialized view satisfies each condition of a first set of conditions; and

if said materialized view satisfies each condition in said first set of conditions, then rewriting said query to produce a rewritten query that references said materialized view and that includes a rewritten restriction that restricts said ordered dimension at said second level of granularity.

11. The computer-readable medium of claim 10, wherein said step of rewriting includes determining whether bounds of at least one coarser granule of said coarser granules coincide to bounds of said restriction.

12. The computer-readable medium of claim 11, wherein the step of rewriting includes if bounds of at least one coarser granule of said coarser granules coincide to bounds of said restriction, then rewriting said query to produce a rewritten query that references said at least one coarser granule.

13. The computer-readable medium of claim 11, wherein the step of rewriting includes rewriting said query to produce a union between a plurality of queries that include a first query and a second query, wherein:

the first query references said materialized view and includes a restriction based on one or more of the coarser granules, and

a second aggregate query that reflects data requested by said aggregate query but not data requested by said first query.

14. The computer-readable medium of claim 13, wherein the step of rewriting said query to produce a union between a plurality of queries includes producing said second aggregate query as an aggregate query that places a restriction on said ordered dimension, said restriction referencing at least one granule at said first level of granularity.

15. The computer-readable medium of claim 10, wherein ordered dimension metadata defines said ordered dimension;

wherein the step of determining whether said materialized view satisfies each condition of a first set of conditions includes examining said ordered dimension metadata to determine whether said materialized view satisfies a condition that said materialized view groups results by coarser granules for the ordered dimension; and

wherein the step of rewriting is based on said ordered dimension metadata.

16. The computer-readable medium of claim 10, wherein the step of receiving an aggregate query includes receiving an aggregate query that places a restriction that limits to a set of granules defined by one or more dimension tables.

17. The computer-readable medium of claim 10, wherein the step of receiving an aggregate query includes receiving an aggregate query that places a restriction on an ordered time dimension;

and

wherein the step of determining whether said materialized view satisfies each condition of a first set of conditions includes determining whether said materialized view satisfies a condition that said materialized view groups results by coarser granules for the ordered time dimension.

18. The computer-readable medium of claim 10, wherein the computer-readable medium further including sequences of instructions for performing the step of determining that a second materialized view satisfies a second set of conditions;

and

wherein the first set of conditions includes that said second level of granularity by which said first materialized view groups results is coarser then the level of granularity of the ordered dimension at which said materialized view groups results.

19. A database system that processes queries, comprising:

said database system configured to receive an aggregate query that places a restriction on an ordered dimension;

wherein said restriction is specified at a first level of granularity for the dimension;

wherein said aggregate query does not reference a materialized view that groups results at a second level of granularity of the ordered dimension;

wherein the second level of granularity is coarser than said first level of granularity;

said database system configured to determine whether said materialized view satisfies each condition of a first set of conditions; and

said database system configured to rewrite said query, when said materialized view satisfies each condition in said first set of conditions, to produce a rewritten query that references said materialized view and that includes a rewritten restriction that restricts said ordered dimension at said second level of granularity.

20. The database system of claim 19, wherein said database system is configured to rewrite by determining whether bounds of at least one coarser granule of said coarser granules coincide to bounds of said restriction.