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

• US 7,912,834 B2
• Filed: 05/19/2006
• Issued: 03/22/2011
• Est. Priority Date: 03/26/2002
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gupta, Abhinav, Witkowski, Andrew
• Primary Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US11/437,072
• Publication Number:
  US 20060212436A1
• Time in Patent Office: 1,768 Days
• Field of Search: 707/2, 707/3, 707/4, 707/5, 707/201, 707/717, 707/713, 707/759, 707/714, 707/E17.017, 707/E17.063, 707/999.002
• US Class Current: 707/717
• 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:

a database computer system performing 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;

query to reference a materialized view;

wherein said criteria for rewriting the query includes that;

a) the materialized view ranks rows based on said particular ranking criteria; and

b) N is less than or equal to a minimum threshold number associated with the materialized view;

wherein said materialized view is incrementally refreshed in a manner that guarantees that after incrementally refreshing, said materialized view contains a number of ranked rows in said groups of rows that is least as said minimum threshold number;

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 by field.

4. A computer-readable volatile or non-volatile medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform:

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 the query to reference a materialized view;

wherein said criteria for rewriting the query includes that;

a) the materialized view ranks rows based on said particular ranking criteria; and

b) N is less than or equal to a minimum threshold number associated with the materialized view;

wherein said materialized view is incrementally refreshed in a manner that guarantees that after incrementally refreshing, said materialized view contains a number of ranked rows in said groups of rows that is least as said minimum threshold number;

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.

5. A computer-readable volatile or non-volatile medium as recited in claim 4, 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.

6. A computer-readable volatile or non-volatile medium as recited in claim 4, 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.