Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view

• US 20060212436A1
• Filed: 05/19/2006
• Published: 09/21/2006
• Est. Priority Date: 03/26/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gupta, Abhinav, Witkowski, Andrew
• Granted Patent: US 7,912,834 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

Techniques are provided for improving efficiency of database systems, and in particular, to refreshing materialized views maintained by database systems and rewriting queries to access the materialized views. According to the approaches, a ranked materialized view is incrementally refreshed, and during the incremental refresh operation, rows in the partitions of the materialized view are ranked within the partitions.

Claims

1. A method of rewriting queries, the method comprising the steps of:

receiving a query that requests N top ranking rows from groups of rows in a base table, wherein said ranking is based on a particular ranking criteria;

determining whether said received query satisfies one or more criteria for rewriting, wherein said criteria include that N be less than a minimum threshold number associated with a materialized view; and

if said received query satisfies said one or more criteria for rewriting, then rewriting said query to produce a rewritten query that references the materialized view.

2. The method of claim 1, wherein:

said N top ranking rows are ranked based on a field in the base table; and

wherein said one or more criteria include that the materialized view rank rows based on said field.

3. The method of claim 1, wherein:

said query requests N top ranking rows from groups of rows that are partitioned based on a field; and

said one or more criteria include that said materialized view contains partitions of rows partitioned on said field.

8. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

9. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

10. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

4. A method of rewriting queries, the method comprising the steps of:

receiving a query that requests ranked results of an aggregate function applied to groups of rows from;

a base table, or

a join of base tables;

determining whether said received query satisfies one or more criteria for rewriting; and

if said received query satisfies said one or more criteria, then rewriting said query to produce a rewritten query that references a materialized view.

5. The method of claim 4, wherein said aggregate function is either the SUM function, MAX function, MIN function, COUNT function, or AVG function.

6. The method of claim 4, wherein said materialized view is a nested materialized view reflecting rankings of rows from a summary table that summarizes data based on:

said aggregate function, and

said base table or said join of base tables.

11. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

12. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

13. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

7. A method of rewriting queries, the method comprising the steps of:

receiving a query that requests results of an aggregate function applied to groups of rows in a base table, wherein said aggregate function is one of a MAX function or MIN function;

determining whether said received query satisfies one or more criteria for rewriting; and

if said received query satisfies said one or more criteria, then rewriting said query to produce a rewritten query that references a materialized view.

14. A computer-readable medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.