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

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

Info

Publication number
US20060212436A1
US20060212436A1 US11437072 US43707206A US2006212436A1 US 20060212436 A1 US20060212436 A1 US 20060212436A1 US 11437072 US11437072 US 11437072 US 43707206 A US43707206 A US 43707206A US 2006212436 A1 US2006212436 A1 US 2006212436A1
Authority
US
Grant status
Application
Patent type
Prior art keywords
rows
materialized view
query
processors
table
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Granted
Application number
US11437072
Other versions
US7912834B2 (en )
Inventor
Abhinav Gupta
Andrew Witkowski
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Oracle International Corp
Original Assignee
Oracle International Corp
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30442Query optimisation
    • G06F17/30448Query rewriting and transformation
    • G06F17/30457Query rewriting and transformation to use cached/materialised query results
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99932Access augmentation or optimizing
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99951File or database maintenance
    • Y10S707/99952Coherency, 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.

Description

    RELATED APPLICATION
  • This application is a divisional of and claims priority to U.S. patent application Ser. No. 10/107,106, entitled incremental Refresh of Materialized Views Containing Rank Function, and Rewrite of Queries Containing Rank or Rownumber or Min/Max Aggregate Functions Using Such A Materialized View, filed on Mar. 26, 2002 by Abhinav Gupta, et al., the contents of which are incorporated herein by reference.
  • This application is related to U.S. patent application Ser. No. 10/059,616, entitled Incremental Refresh of Materialized Views with Joins and Aggregates after Updates to Multiple Tables, filed by Shilpa Lawande, Abhinav Gupta, Benoit Dageville on Jan. 28, 2002, having the attorney docket no. 50277-1805, herein referred to as Lawande and incorporated by reference.
  • FIELD OF THE INVENTION
  • The present invention relates to database systems, and in particular, to maintaining materialized views.
  • BACKGROUND OF THE INVENTION
  • The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
  • In a database management system (DBMS), data is stored in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In relational database systems, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
  • The present invention is not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the terms “table”, “row” and “column” shall be used herein to refer respectively to the data container, record, and field.
  • In a database used for “data warehousing” or “decision support”, it is common for identical or closely related queries to be issued frequently. For example, a business may periodically generate reports that summarize the business facts stored in the database, such as: “What have been the best selling brands of soft drinks in each of our sales regions, during the past six months?”.
  • To respond to such queries, the database server typically has to perform numerous joins, aggregation and ranking operations. The join operations are performed because the database records that contain the information that is required to respond to the queries are often organized into a star schema. A star schema is distinguished by the presence of one or more relatively large tables and several relatively smaller tables. Rather than duplicating the information contained in the smaller tables, the large tables contain references (foreign key values) to rows stored in the smaller tables. The larger tables within a star schema are referred to as “fact tables”, while the smaller tables are referred to as “dimension tables”. The aggregation operations are performed to compute sum of sales and ranking to get the top selling brands.
  • When a database management system contains very large amounts of data, certain queries against the database can take an unacceptably long time to execute.
  • Materialized Views
  • Among commercial users of database systems, it has become a common practice to store the results of often-repeated queries in database tables or some other persistent database object. By storing the results of queries, the costly operations required to generate the results do not have to be performed every time the queries are issued. Rather, the database server responds to the queries by simply retrieving the pre-computed data.
  • These stored results are commonly referred to as materialized views. The contents of a materialized view is defined by metadata referred to as a view definition. The view definition contains mappings to one or more columns in the one or more tables containing the data. Typically, the view definition is in the form of a database query.
  • Columns and tables that are mapped to a materialized view are referred to herein as base columns and base tables of the materialized view, respectively. The column and the base column mapped to the column are referred to as being the same field. The data maintained in the base columns is referred to herein as base data. The data contained in a materialized view is referred to herein as materialized data.
  • Materialized views eliminate the overhead associated with gathering and deriving the data every time a query is executed. Computer database systems that are used for data warehousing frequently maintain materialized views that contain pre-computed summary information in order to speed up query processing. Such summary information is created by applying an aggregate function, such as SUM, COUNT, or AVERAGE, to values contained in the base tables. Materialized views that contain pre-computed summary information are referred to herein as “summary tables” or more simply, “summaries”.
  • Summary tables typically store aggregated information, such as “sum of PRODUCT_sales, by region, by month.” Other examples of aggregated information include counts of tally totals, minimum values, maximum values, and average calculations.
  • Another form of pre-computed information stored in materialized views is materialized data reflecting the rankings of rows from the base table, where the ranking may be based on values of one or more columns. The materialized view contains a column with pre-computed values reflecting rankings. Furthermore, the ranking of a row may reflect its rank relative to a particular subset of rows of the materialized view. Thus, a single materialized view may have many such subsets, where the rows of each subset are ranked relative to the rows in the same subset, and independently of the rows in the other subsets. The various ranked subsets can by generated by grouping the rows of the materialized view based on values that they have in a particular column. A group of rows with values that satisfy one or more criteria is referred to herein as a logical partition, or simply a partition.
  • For example, a materialized view may contain logical partitions that are formed by grouping rows according to values in the “region” column. The materialized view also contains a column RANKING. RANKING contains values representing a row's ranking within its respective partition, where the ranking is based on values in column PRODUCT_sales. One particular partition contains the rows having the value ‘WEST’ in region. A row in the partition with the value 1 in the column RANKING has the highest value in PRODUCT_sales relative to other rows in the partition. A materialized view having materialized data reflecting a ranking of rows from another table is referred to herein as a ranked materialized view.
  • Query Rewrite
  • Through a process known as query rewrite, a query can be optimized to recognize and use existing materialized views that could answer the query. Typically, the query rewrite optimization is transparent to the application submitting the query. That is, the rewrite operation happens automatically and does not require the application to know about the existence of materialized views, nor that a query that accesses a particular materialized view has been substituted for the original query.
  • Refreshing Materialized Views
  • As new data is periodically added to the base tables of a materialized view, the materialized view needs to be updated to reflect the new base data. When a materialized view accurately reflects all of the data currently in its base tables, the materialized view is considered to be “fresh”. Otherwise, the materialized view is considered to be “stale”. A stale materialized view may be recomputed by various techniques that are collectively referred to as “refresh”.
  • Data loading and refresh of materialized views typically takes place during off-hours when the data warehouse is in a controlled period of little activity. The data loading and refresh is restricted to a time period called the refresh window during which the system can be dedicated to refresh. The refresh window is typically allowed to be no more than four to six hours. Refresh may be deferred until the end of the week or month, so that loading of additional detail data may occur much more frequently than refresh.
  • One approach to refreshing materialized views is referred to as the “total refresh” or “complete refresh” approach. According to the complete refresh approach, during each refresh operation, all values stored in a stale materialized view are discarded and recalculated based on all of the base data. Systems that employ the complete refresh approach have the disadvantage that the re-creation process is a relatively lengthy operation due to the size and number of tables from which the materialized data is derived. For example, when ten new rows are added to a particular base table that contains a million rows, a complete refresh operation would have to process all one million and ten rows of the base table to regenerate the materialized views derived using the base table.
  • The process of updating materialized data may be improved by performing an incremental refresh, where rather than generating a new set of materialized data based on calculations that use all of the base data, the materialized data is updated based on just the new base data (i.e. the changes made to the base tables subsequent to the most recent refresh operation).
  • Not all types of materialized views are incrementally refreshed. For example, there is no mechanism that incrementally refreshes a ranked materialized view. The primary reason for this is that when the value of a column in a row of a base table is modified, all rows against which the row has been ranked must be sorted to determine the new ranking within the partition. For example, if the column by which a row is ranked within a partition were changed, all rows in the partition would have to be sorted again to determine a new ranking.
  • Based on the foregoing, it is clearly desirable to provide a mechanism that incrementally refreshes ranked materialized views, and to provide a query rewrite mechanism to access such materialized views.
  • SUMMARY OF THE INVENTION
  • Techniques are provided for improving efficiency of database systems, and in particular, for refreshing materialized views maintained by database systems and rewriting queries to access the materialized views. According to an aspect of the present invention, 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. A partition is a group of rows with values that satisfy one or more criteria. For example, a partition is a group of rows with matching values in a set of columns in the PARTITION BY clause of the RANK function.
  • Incremental refresh of a ranked materialized view is performed by only examining the updates, insertions, and deletions to the base table that have occurred since the ranked materialized view was last refreshed or created. Furthermore, the ranked materialized view is refreshed in a manner that guarantees that a partition in the materialized view, which corresponds to a partition in a base table, contains rows that correspond to a threshold minimum number of top ranked rows from the corresponding partition in the base table. Thus, assuming a threshold minimum number of 100 rankings, the incremental refresh is performed in a manner that guarantees that, after the incremental refresh operation, a partition in the materialized view includes at least the 100 highest ranked rows from the corresponding partition in the base table.
  • Queries requesting the X top ranked rows for the corresponding partition in the base table may be rewritten to access the ranked materialized view if X is less than or equal to the minimum threshold used by the materialized view.
  • During an incremental refresh, the rows within a partition of a ranked materialized view may be removed, leaving the partition with a ranking count that is less than the threshold minimum number. If, after an incremental refresh, the threshold minimum number can no longer be guaranteed, then a complete refresh of the partition is performed. To forestall the need to perform a complete refresh, a ranked materialized view can be created so that its partitions have a ranking count that may be as great as another threshold number that is greater than the minimum threshold number.
  • BRIEF DESCRIPTION OF THE DRAWINGS
  • The present invention is illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which like reference numerals refer to similar elements and in which:
  • FIG. 1A is a block diagram depicting a table used to illustrate an embodiment of the present invention;
  • FIG. 1B is a diagram depicting a materialized used to illustrate an embodiment of the present invention;
  • FIG. 2 is a flow chart depicting a process for incrementally refreshing a materialized view according to an embodiment of the present invention;
  • FIG. 3A is a block diagram depicting a table used to illustrate an embodiment of the present invention;
  • FIG. 3B is a diagram depicting the state of a materialized view at a particular stage of an incremental refresh process according to an embodiment of the present invention;
  • FIG. 3C is a diagram depicting the state of a materialized view at a particular stage of an incremental refresh process according to an embodiment of the present invention;
  • FIG. 3D is a diagram depicting the state of a materialized view at a particular stage of an incremental refresh process according to an embodiment of the present invention;
  • FIG. 4A is a block diagram depicting an incremental refresh mechanism according to an embodiment of the present invention;
  • FIG. 4B-1 is a diagram depicting a log used to track changes to base tables for an incremental refresh mechanism according to an embodiment of the present invention;
  • FIG. 4B-2 is a diagram depicting a view according to an embodiment of the present invention;
  • FIG. 4C-1 is a diagram depicting a log used to track changes to base tables for an incremental refresh mechanism according to an embodiment of the present invention;
  • FIG. 4C-2 is a diagram depicting a view according to an embodiment of the present invention;
  • FIG. 5A is a flow chart depicting a process for incrementing a materialized view according to an embodiment of the present invention;
  • FIG. 5B is a diagram illustrating a query executed as part of a process for incrementing a materialized view according to an embodiment of the present invention;
  • FIG. 5C is a diagram illustrating a query executed as part of a process for incrementing a materialized view according to an embodiment of the present invention;
  • FIG. 5D is a diagram illustrating a query executed as part of a process for incrementing a materialized view according to an embodiment of the present invention;
  • FIG. 5E is a diagram illustrating a query executed as part of a process for incrementing a materialized view according to an embodiment of the present invention; and
  • FIG. 6 is a block diagram of a computer system used to implement an embodiment of the present invention.
  • DETAILED DESCRIPTION OF THE INVENTION
  • A method and apparatus for incrementally refreshing materialized views is described. In the following description, for the purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. It will be apparent, however, that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form in order to avoid unnecessarily obscuring the present invention.
  • Overview
  • Described herein are approaches for incrementally refreshing a ranked materialized view. The approach involves creating a materialized view with rows ranked within partitions. The whole materialized view can also be viewed as a single partition, in which case the rows are globally ranked in the table. Each row in a partition of the materialized view corresponds to a row in the corresponding partition of the base table.
  • Incremental refresh of a ranked materialized view is performed by only examining the updates, insertions, and deletions to the base table that have occurred since the ranked materialized view was last refreshed or created. Furthermore, the ranked materialized view is refreshed in a manner that guarantees that a partition contains rows that correspond to a threshold minimum number of top ranked rows from the corresponding partition in the base table. A threshold minimum number may be, for example, 100 rankings. The number of top rankings that are represented at any given time by the rows of a partition in the materialized view is referred to herein as the ranking count of that partition. A materialized view partition that contains rows that correspond to the 100 top ranked rows from the corresponding partition in the base table has a ranking count of 100. Some rows may share the same rank, and therefore the top ranked 100 rows may include more than 100 rows. Because, for a given partition, the materialized view has a ranking count of at least the top 100 rows, queries requesting not more than the 100 top ranked rows for the corresponding partition in the base table may be rewritten to access the ranked materialized view.
  • During an incremental refresh, the rows within a partition of a ranked materialized view may be removed, leaving the partition with a ranking count that is less than the threshold minimum number. If, after an incremental refresh, the threshold minimum number can no longer be guaranteed, then a complete refresh is performed. To forestall the need to perform a complete refresh, a ranked materialized view can be created so that its partitions have an initial ranking count that is substantially greater than the minimum threshold number. The desired initial ranking count is referred to as the maximum threshold number.
  • Queries that may be rewritten to access the ranked materialized views are not limited to queries requesting ranked information. Specifically, aggregate queries that request the application of the MIN or MAX function may be rewritten to access a ranked materialized view.
  • Illustrative Ranked Materialized View
  • FIG. 1A is a diagram showing a base table and FIG. 1B is diagram showing a materialized view containing data from the base table, which are used to illustrate various embodiments of the invention. Referring to FIG. 1A, it shows table T. Table T contains columns 110 and rows 120. Columns 110 include rowid 111, region 112, sales_person 113, and sales 114. Rowid 111 is a rowid pseudo-column, which has a unique identifier for each row in a table. The rowid 111 pseudo-column may, for example, simply represent the values of the actual storage locations at which the rows are stored.
  • FIG. 1B shows materialized view M1, whose base table is table T. Materialized view M1 includes columns 130 and rows 140. Columns 130 include rid 131, region 132, sales_person 133, sales 134, and rnk 135. The base columns for rid 131, region 132, sales_person 133, and sales 134 are rowid 111, region 112, sales_person 113, and sales 114 respectively. rnk 135 will be explained in further detail below.
  • Partitions 142 and 144 are groupings of rows within materialized view M1. Partitions 142 and 144 are partitioned according to the values in region 132. Partition 142 contains rows that contain the value ‘W’ in the column region 132. Partition 144 is a group of rows that contain the value ‘E’ in column region 132.
  • Column rnk 135 contains ranking values. A ranking value for a particular row indicates the row's rank within a partition. A ranking may be based on an ascending or descending order of values of one or more columns. These values upon which a ranking is based are referred to as ranked values.
  • The rows in partitions 142 and 144 are ranked according to sales 134. For row R1 in materialized view M1, the ranking value ‘1’ in rnk 135 indicates row R1 is ranked first within partition 142. Row R1 has the highest value in column sales 134 in partition 142. Row R3, having the lowest value in column sales 134 among the rows in partition 142, has the lowest ranking value.
  • The following is database command statement CM1, which specifies a definition for materialized view M1 according to an embodiment of the present invention. A database system generates metadata defining a materialized view in response to receiving a database command such as database command statement CM1.
    CREATE MATERIALIZED VIEW M1
    AS
    SELECT rid, region, sales_person, sales, rnk
    FROM
    ( SELECT rowid rid, region, sales_person, sales,
    RANK ( ) OVER (PARTITION BY region
    ORDER BY sales DESC) rnk
    FROM T
    )
    WHERE rnk <= r1 OR KEEP(rnk<=r2);
  • The syntax of query CM1 defines materialized view M1 as follows. The Select clause in CM1 defines materialized view M1 to have columns that correspond to base columns in table T that are returned by the inner query of CM1.
  • Another column defined by materialized view M1 for CM1 is rnk 135. For a particular row in M1, the value in rnk 135 is equal to the function RANK referenced in CM1. During execution of the inner query to create materialized view M1, the RANK function generates a value reflecting a rows rank in the logical partitioning defined by the window clause “(PARTITION BY region ORDER BY sales DESC)”. A window clause defines a logical partition and ordering for the purpose of computing a function which in the present case is the RANK function.
  • The window clause is itself composed of various subclauses. The subclause “PARTITION BY REGION” is a partition-by clause that specifies the column(s) containing values, by which to partition rows, which in this case, is column region. The subclause “ORDER BY sales” is an order-by clause that specifies the column upon which to base a ranking within a partition, which in this case is column sales. The clause DESC specifies the type of ordering the ranking is based on, which in this case is the descending ordering.
  • The outer query includes several clauses used to establish the lower threshold number and maximum threshold number for the partitions in materialized view M1. The “KEEP(rnk<=r2)” defines the maximum threshold number to be r2. The “WHERE rnk<=r1” clause includes a predicate that defines the lower threshold number to be r1. In the particular example discussed, r1=3 and r2=6.
  • The syntax described for CM1 should be regarded as illustrative rather than limiting. The present invention is not limited to any particular syntax or technique for defining the attributes of a ranked materialized view, and in particular, to specifying the minimum threshold number and maximum threshold number.
  • Overview of Incremental Refresh Process
  • FIG. 2 is a flow chart showing an overview of a process for incrementally refreshing a materialized view. The steps are performed on a particular partition. The steps are illustrated using table T and partition 142 in materialized view M1. At various points during the illustration of the process for incrementally refreshing a partition, the state of partition 142 is shown in FIGS. 3B, 3C, and 3D. The process is illustrated using rows that are deleted or inserted into the base table. However, the process is applicable to materialized views based on base tables whose rows are updated, as shall be described in greater detail.
  • FIG. 3A shows rows in base table T that are deleted and inserted in the illustration. Rows that are deleted are lined-out. Thus rows R1 and R3 are deleted. Rows that are inserted are bordered by a dashed line. Thus rows R29 and R30 are inserted.
  • In illustrations that follow, a notation is used to specify column values for rows. The notation is explained according to the following example. In the phrase “R1 (W, 200)”, “(W,200)” specifies column values for row R1 in table T, denoting that the value for column region 112 is ‘W’ and the value for column sales 114 is 200.
  • In addition, a row is referred to as being qualified for a partition in a materialized view, if the row belongs to the corresponding partition in the base table. For example, row R14 (W, 80) is qualified for partition 142. Row R14 has the value ‘W’ for column region 112 and belongs to the partition of rows in table T that have the same value for the column region 112. R14 qualifies for partition 142 even though partition 142 contains no row that corresponds to row R14.
  • At step 210, rows that correspond to rows deleted from table T since the last refresh of the partition are deleted from the partition in the materialized view. In the current illustration, rows R1 (W, 200) and R3 (W, 150) in table T have been deleted. Therefore, the corresponding rows in partition 142 are deleted.
  • FIG. 3B shows the corresponding rows in materialized view M1 that have been deleted. The row in materialized view M1 that corresponds to a particular row in table T is the row in materialized view M1 that has the value in column rid 131 equal to the rowid of the particular row in table T. The row in materialized view M1 that corresponds to row R1 in table T is the top row shown in partition 142.
  • At step 220, the minimum ranked value remaining in partition 142 is determined. The minimum ranked value in partition 142 is ‘160’.
  • At step 230, rows that correspond to a qualifying row inserted into the base table since the last refresh, and that have at least the minimum ranked value in sales 114, are inserted into the partition. In the current illustration, rows R29 (W, 175) and R30 (W, 145) were added to table T. Both R29 and R30 have value ‘W’ in column sales 114. However, only a row for row R29 is added to partition 142 because among the pair of rows only row R29 has a value in column sales that is greater or equal to ‘160’.
  • For the rows in partition 142 that have at least the minimum ranked value in sales 134, it is possible to determine the ranking for the corresponding rows in the corresponding partition of the base table by only examining the rows in partition 142. This determination is possible because partition 142 contains two sets of rows that together represent all rows in the base table that can possibly qualify for partition 142 and that have a value of ‘160’ or greater in sales 114. The first set includes rows that correspond to all rows in the base table that were not deleted since the last refresh, and that at the moment of the last refresh, qualify for partition 142 and have a value of ‘160’0 or greater in sales 114. The second subset of rows includes rows that correspond to all rows that have been inserted after the last refresh, and that have a value of ‘160’ or greater in sales 114.
  • At step 240, it is determined whether the ranking count of the partition is less than the minimum threshold number. If the ranking count is less than the minimum threshold, then at step 250, a complete refresh is commenced. Otherwise at step 260, the partition is re-ranked and the ranking values in column rnk 135 are re-calculated accordingly.
  • In the current illustration, the minimum threshold number is 3. In partition 142, the ranking count is five. Therefore, execution of the steps proceeds to step 260, where partition 142 is re-ranked and column rnk 135 in materialized view M1 is recalculated. FIG. 3D shows partition 142 after performing step 260.
  • In database systems, rows may be deleted, inserted, or updated. For the purposes of incrementally refreshing a materialized view according to approaches described herein, an update of a row is treated as a deletion of the row with its old values (pre-update values) and an insertion of a row with the new column values. For example, if row R1 had been updated to have a value ‘100’ for sales 114, the update would be processed as a delete of row R1 with value ‘200’ for column sales 114 as described above, and an insert of row R1 with a new updated value of ‘100’ for column sales 114.
  • Illustrative Implementation
  • FIG. 4A shows an incremental refresh mechanism according to an embodiment of the present invention. Referring to FIG. 4A, materialized view refresh process 401 is a computer process that both completely refreshes and incrementally refreshes a materialized view M1. The process may be invoked at periodic intervals or upon the occurrence of various events. For example, whenever a transaction is committed, where the transaction modifies the base table for the materialized view.
  • To incrementally refresh a ranked materialized view, information is needed about changes that have occurred to the base table between refreshes. Logs RLog and BLog are log tables that contain information recording such changes to table T. The information is extracted by materialized view refresh process 401 from log RLog through queries issued against view vRLog, and from log BLog through queries issued against view vBLog. The log tables are maintained by the database management system that manages table T.
  • FIGS. 4B are diagrams that depict log RLog and view vRLog in greater detail. Log RLog includes rows 440 and columns snaptime$$ 435, oldnew 436, rid 431, region 432, sales_person 433, and sales 434. Log RLog is a row-based log because its rows record changes to a particular row in a particular table, referred to herein as the master table. In this case, table T is the master table. A row in a row-based log is referred to as a log record. Column snaptime$$ 435 contains values that specify the time a change occurred.
  • RLog contains two types of log records. One type contains the values in rows in the master table that are deleted or updated. For this type of log record, column oldnew 436 is set to ‘O’. A log record for a row that has been deleted contains the values that existed for a row when the row was deleted. A log record for a row that has been updated contains the values for the row that existed when the row was updated. For example, row 411 in FIG. 4B is a log record for an update to row R1 in table T, where the value for sales 114 is changed from ‘200’ to ‘100’. Columns region 432, sales_person 433, and sales 434 contain the old values, that existed before the update, in columns region 112, sales_person 113, and sales 114 in table T. Column rid 431 contains the row-id of the row. Column snaptime$$ 435 is set to the time of update, which is t1. Oldnew is set to ‘O’.
  • The second type of log record records information for rows in the master table that are inserted and updated. For this type of log record, column oldnew 36 is set to ‘N’. A log record for a row that has been inserted contains the values to which the row's columns are set when the row is inserted. A log record for a row that has been updated, contains values for the row that existed right after the insert. In the current example for row R1 in table T, row 412 is the log record generated for the update. Columns region 432, sales_person 433, and sales 434 contain the values that existed right after the update in columns region 112, sales_person 113, and sales 114 in table T. Column rid 431 contains the row-id 111 of the row in Table T. Column snaptime$$ 435 is set to the time of the update, which is t1. oldnew is set to ‘N’.
  • FIG. 4B-2 shows the definition of view vRLog. The WHERE clause contains predicates that specify criteria that is only satisfied by log records in log RLog that reflect changes that have occurred between the last refresh time and the current time. Values for these times are stored as variables :last_refresh_time and :current_refresh_time.
  • FIG. 4C-1 shows log BLog in greater detail. Log BLog records information about bulk inserts into one or more tables. A bulk insert refers to the capability of some databases to add multiple rows contained in a group of data blocks to a table in a single “bulk insert operation”, by “simply” incorporating the group of datablocks as part of the set of datablocks that a database system uses to hold rows for the table. A group of datablocks that may be incorporated in this manner include, without limitation, an extent, which is composed of multiple datablocks stored contiguously in a datafile.
  • Log BLog contains rows 462 and columns tableobj 472, timestamp 474, lowrowid 476, and highrowid 478. Rows 462 are composed of one or more log records that each record information about a particular bulk insert operation. Column tableobj 472 contains values that identify the table to which the rows were bulk inserted. The value may be, for example, an object identifier used by a database system to identify the particular table. Column timestamp 474 contains values that specify the time the bulk insert occurred.
  • Rows that are bulk inserted exclusively occupy a range of row-ids. A value in column lowrowid 476 identifies the beginning of a range, column highrowid 478 identifies the end of a range.
  • For example, row 464 records a bulk insert into table T. The value objectid_T in column tableobj 472 identities table T, the value t2 in column timestamp 474 specifies the time of the bulk insert, the value ‘R10000’ in lowrowid 476 specifies the beginning of a range of row-ids, the value ‘R20000’ in highrowid 478 specifies the end of the range of row-ids.
  • FIG. 4C-2 shows the definition of view vBLog. View vBLog is a join view between Blog and table T. View vBlog returns rows from table T that have been bulk inserted between the last refresh time exclusively and the current refresh time inclusively. The rows returned from view vBLog have the same shape as the rows returned from vRLog, that is, the rows have a sequence of columns that have the same datatype and column identifier. View vBLog specifies a value ‘N’ for column oldnew, thus a result set for vBLog always contains ‘N’ in column oldnew. The result set is formed by joining rows from table T with log records in Blog that record a bulk insert to table T where the bulk insert occurred between the last refresh time exclusively and the current refresh time inclusively. The join condition is that a row in table T has a value column for rowid that lies within a row-id range specified by a log record from BLog.
  • Illustrative Process for Incrementally Refreshing Materialized View
  • FIG. 5A shows the three phases of a process for incrementally refreshing a materialized view according to an embodiment of the present invention. Each phase is performed by executing a single database command that substantially conforms to SQL. These phases include delete phase 502, insert phase 504, and update phase 506. FIG. 5B shows database command QDelete, which is executed for delete phase 502. FIG. 5C shows database command QInsert, which is executed for insert phase 504. FIG. 5D shows database command QUpdate, which is executed for update phase 506.
  • During delete phase 502, materialized view refresh process 401 deletes the rows in the materialized view M1 that correspond to (1) the rows that have been deleted or updated in the base table since the last refresh and (2) the rows in M1 where rnk 135 equals NULL. As will be explained in further detail, column rnk 135 for a given row may be set to NULL during execution of the UPDATE PHASE. A row with this value for this column is treated as being unranked and deleted in the delete phase 502 of a subsequent refresh.
  • Referring to FIG. 5B, database command QDelete is executed to delete the rows in materialized view M1 for delete phase 502. Where clause 521 includes predicates 522 and 524, which define criteria satisfied by the rows to delete from M1.
  • The criterion defined by predicate 522 is satisfied by rows in M1 having a value for column rid 131 that equals the row-id of a row in the result set of view vRLog. A subset of the result set of view vRLog with oldnew=O contains rows deleted or old values of rows updated from base table T since the last refresh.
  • The criterion defined by predicate 524 is satisfied by rows in M1 where column rnk 135 contains the NULL value and is included to remove rows marked as “refresh deleted” (i.e. rank=null) in the previous refresh.
  • During the insert phase 504, materialized view refresh process 401 inserts into materialized view M1 the rows from base table T that have been updated or inserted into base table T since the last refresh. Referring to FIG. 5C, database command QInsert is executed to perform insert phase 504. Execution of QInsert inserts into materialized view M1 rows from table T that satisfy the criteria defined by predicate 542 in where clause 540. Rows that satisfy this criteria are the rows in table T having a row-id value found in the union of several result sets, and in particular, found in column rid of a rows in the union. The union contains a row for every row inserted or updated in base table T since the last refresh.
  • The first of the several result sets is the result set of inner query 526. Inner query 526 references view vRLog. The rows from vRLog include the log records generated between refreshes. Of these log records, the WHERE clause predicate of inner query 526 filters those log records corresponding to inserts and log records with new values of updated rows. Thus, the first result set includes rows having a rid column that contains row-ids of rows updated and inserted in table T since the last refresh.
  • The second result set is defined by inner query 528. Inner query 528 references the rid column of view vBLog. The rows returned by view vBLog include rows in table T bulk inserted into table T since the last refresh. Thus, the second result set includes rows with a column rid that contains rows-ids of rows bulk inserted since the last refresh.
  • During update phase 506, materialized view refresh process 401 updates ranking values in column rnk 135 in the rows of “affected partitions” to reflect the current rankings. An “affected partition” is a partition containing rows whose ranking may be affected by inserts, deletes, and updates of rows in the base table since the last refresh. The rows are updated by changing ranking values in rnk 135 to reflect the new rankings within an affected partition. For rows having a value in sales 134 that is less than the minimum ranked value, the value in rnk 135 is set to NULL, which in effect, leaves the rows unranked. These rows are deleted during the delete phase 502 of the next execution of the incremental refresh process.
  • Referring to FIG. 5D, the update phase 506 is executed to update rows in affected partitions. The update is performed using inline view JV 562. JV 562 returns rows from the affected partitions in materialized view M1, where the rows have a ranking less than or equal to the higher threshold number ‘r2’. Each row has a value for column rnk 135 and column new_rnk. The value for new_rnk is the rows' re-calculated ranking value.
  • Inline view JV is a join between materialized view M1 and inline view V 564. The predicates 572 of inline view JV 562 filter rows from the join that have (1) values for sales 134 that are greater or equal to the minimum ranked value and (2) values for new_rnk less than or equal to maximum threshold number ‘r2’.
  • Inline view V 564 returns rows from affected partitions in M1, the rows having columns rid 131, region 132, sales 134, min_sales, and new_rnk. Predicate 580 of inline view V 564 filters rows from M1 containing a value in region 132 found in the region column of any row from views vRLog or vBLog. In effect, predicate 580 filters rows that are in an affected partition.
  • Min_sales is the value of the MIN function 576, which when computed, returns the minimum value in column sales 134 found in a subset of rows in a partition of materialized view M1, where the subset contains rows having non-NULL ranking values in column rnk 135. This subset contains only rows that resided in a partition before the current incremental refresh and after execution of delete phase 502. The CASE statement in MIN function 576 limits the rows on which the minimum calculation is based to this subset.
  • The column new_rnk is equal to the value of RANK function 578. This function returns a ranking value based on values in sales 134 for a particular partition.
  • Detecting Complete Refresh Conditions
  • According to an embodiment of the present invention, a complete refresh of materialized view M1 is commenced when, after performing update phase 506, the ranking count of at least one partition is less than the threshold minimum number requirement. Determining whether this condition exists is an operation that may be performed by executing a separate query that generates a result indicating that at least one partition in the materialized view does not satisfy the minimum threshold requirement. In response to detecting this condition, refresh process 401 commences a complete refresh of the materialized view M1.
  • Alternately, a mechanism for detecting the condition may be incorporated in the database command that is executed to perform update phase 506. FIG. 5E shows a database command QUpdate', which is a modified version of database command QUpdate. Unlike QUpdate, QUpdate' includes function ASSERT 590 in the Select list of inline view JV 562. The function generates an error condition if the argument boolean condition evaluates to FALSE, i.e., the ranking count of at least one partition in M1 is less the threshold minimum number ‘r1’. The error condition is detected by refresh process 401. In response to detecting the error condition, refresh process 401 commences execution of the complete refresh process in lieu of the incremental refresh process.
  • Caveat With NULL Values:
  • It is possible that a column upon which a ranking is based may be NULL. If NULLS in the column are ranked higher than non-NULL values, then the ranking values generated for a materialized view must account for this sort of ranking.
  • For example, sales 114 may contain NULL values. The window clause 582 in QUpdate may define an ordering where NULL values are first in order. The clause in QUpate that contains RANK function 578 and window clause 582 may be rewritten to cause partitions in M1 to reflect an ordering where non-NULL values are ranked higher as follows.
    RANK ( ) OVER (PARTITION BY region ORDER BY sales DESC NULLS FIRST))
  • In addition, predicates based on sales should account for the NULL value being ranked higher than non-NULL values. For example, the predicate (sales>=min_sales) in predicates 572 can be rewritten to the following predicate PRW1.
    (sales>=min_sales OR sales is NULL)
  • Query Rewrite
  • A query that requires up to a threshold minimum number of ranked values from a base table may be rewritten to access a materialized view. Before the query is rewritten, rewrite criteria should be satisfied. Rewrite criteria, includes, without limitation, (1) that the query request no more than the threshold minimum number for the materialized view, and (2) that the materialized view rank along the same column as that requested by the query. If the rewrite criteria is satisfied, then the query may be rewritten.
  • The following query QB1 is provided as an example to illustrate a rewrite of a query. The illustration is based on table T and materialized view M1. Assume the threshold minimum number is ‘r1’=100.
    SELECT rowid rid, region, sales_person, SALE
    RANK ( ) OVER (PARTITION BY region ORDER BY SALE
    DESC)
    rnk
    FROM T
    WHERE rnk <10
  • Query QB1 requests the top ten ranking rows from tables based on the columns sales 114. Because the query requests less than the threshold minimum number defined for materialized view M1, and requests a ranking based on sales, i.e., the same column used to rank rows in M1, QB1 may be rewritten to access materialized view M1. The query may be rewritten to query QB2, as follows.
    SELECT rid, region, sales_person, SALE, rnk
    FROM M1
    WHERE rank < 10
  • Because base table T contains many more rows than materialized view M1, the rewritten query may be executed much more quickly, as it accesses a smaller set of data and avoids expensive sort operations, otherwise required for computation of the rank function.
  • Rewriting Aggregate Queries
  • Aggregate queries that request the application of MAX or MIN functions to groups of rows in a base table may be rewritten to access a ranked materialized view. In general, to be rewritten in this manner, the aggregate query should request application of the MIN or MAX function to the same field by which the ranked materialized view ranks rows, and should group by the same field by which the rows are partitioned in a ranked materialized partitions.
  • To provide an example of such a query, and to illustrate how the query may be rewritten, the following query QAG1 is provided. The illustration is based on table T and materialized view M1.
    SELECT region, MAX(sales)
    FROM T
    GROUP BY region;
  • Query QAG1 requests rows representing the maximum sales per region. Specifically, query QAG1 requests that the MAX function be applied to column sales 114 for groups of rows in base table T, where the rows are grouped by column region 112. Thus, query QAG1 requests application of the MAX function (1) to the same field by which rows are ranked in materialized view M1 and (2) to groups of rows grouped by the same field by which rows are partitioned in the rank function of M1. Query QAG1 may be rewritten to query QAG2, as follows.
    SELECT region, MAX(sales)
    FROM M1
    WHERE rnk = 1
    GROUP BY region;
  • The group-by operator is included in QAG2 because multiple rows in M1 may have the same ranking value in column rnk 135. By including the group-by operator, the result set of query QAG2 includes only one row per group. If the order of the RANK function of query QAG1 placed NULL values before non-NULL values, and rows within a partition of M1 were ordered accordingly so that the query QAG1 could be rewritten to reference M1, then a rewritten query should not contain a predicate “rnk=1” because MAX requires the maximum non-NULL value to be returned.
  • To support rewrites of aggregate functions that apply the MIN and MAX function to the ranked field, two ranked materialized views should be maintained. One ranked materialized view should rank in ascending order, and the other should rank in descending order. Finally, if ranked materialized views need only support rewrite of aggregate queries that request application of the MIN and MAX function, and not to support rewrite of queries requesting ranked rows, then the minimum threshold number need only be 1. This is good for refresh because, the smaller the minimum threshold number, the longer it takes for deletes or updates to cause the number of rows in a partition to fall below this threshold. Hence, complete refresh is required less frequently.
  • Supporting Rankings Based on Aggregations
  • It is not only useful to request rows from a base table ranked by a particular column, but also to request a ranking based on aggregate information based on that field. For example, a table CALL is used to track the calls of customers. Table CALL has the following fields.
  • CUSTOMER_ID, NUMBER, DURATION, CALL_TIME
  • Each row in table CALL represents a call from a customer. For each row, column CUSTOMER_ID contains a value identifying a customer, NUMBER contains a value representing a telephone number, DURATION contains a value representing the duration of the call, and CALL_TIME contains a value representing the time the call was commenced.
  • In addition, the following table CUSTOMER is a dimension table for table CALL. A dimension table stores values for one or more attributes of a set of data, where the set resides in another table. Table CUSTOMER stores values for attribute REGION, each value representing a geographic region of a customer. Table CUSTOMER contains the following columns.
  • CUSTOMER_ID, REGION
  • The column CUSTOMER_ID in table CALL is a foreign key into table CUSTOMER. Useful aggregate information from table CALL includes, for example, the customers having top 100 total call duration time per region. Such information may be requested using the following query QAC1.
    SELECT * FROM
    (
    SELECT C.CUSTOMER_ID, C.region,
    RANK( ) OVER (PARTITION BY C.region
    ORDER BY SUM(CALL_TIME) DESC) rnk
    FROM CALLS, CUSTOMER C
    WHERE CALLS.CUSTOMER_ID = C.CUSTOMER_ID
    GROUP BY C.CUSTOMER_ID, C.region
    )
    WHERE rnk <= 100;
  • To support rewrite of aggregate queries requesting ranked aggregate information, such as query QAC1, nested materialized views may be used. A nested materialized view is a materialized view of another materialized view. In other words, a nested materialized view has as a base table another materialized view. To support rewrite of an aggregate query requesting ranked aggregate information from a table, the query may be rewritten to access a nested materialized view, where the nested materialized view contains ranked rows from another summary table that holds the aggregate information. The following materialized view definitions for materialized views NM1 and NM2 are provided to illustrate a nested materialized view that may be used to rewrite aggregate queries.
    NM1
    CREATE MATERIALIZED VIEW MV_CUST_region_CALLS
    AS
    SELECT C.CUSTOMER_ID, C.region,
    SUM(CALL_TIME) SUM_CALL_TIME,
    FROM CALLS, CUSTOMER C
    WHERE CALLS.CUSTOMER_ID = C.CUSTOMER_ID
    GROUP BY C.CUSTOMER_ID, C.region;
    NM2
    CREATE MATERIALIZED VIEW MV_TOP_100_CUST_region
    AS
    SELECT * FROM
    (
    SELECT region, SUM_CALL_TIME, CUSTOMER_ID
    RANK( ) OVER (PARTITION BY region
    ORDER BY SUM_CALL_TIME DESC) rnk
    FROM MV_CUST_region_CALLS
    )
    WHERE rnk <= 1000 OR KEEP (rnk<=2000);
  • Materialized view NM1 contains aggregate information containing the total call duration time for each customer by region, and in particular, the sum of values in column DURATION for groups of rows grouped by CUSTOMER_ID and region.
  • Nested materialized view NM2 represents the customers having the top 100 total duration time by region, and in particular, rows from materialized view NM2 having at least the top 100 ranked values in SUM_CALL_TIME for each grouping of rows in NM1 grouped by column region.
  • Aggregate query QAC1 may be rewritten to access nested materialized view NM2 by rewriting QAC1 to the following rewritten query.
    Select CUSTOMER_ID, region, rnk
    FROM MV_TOP_100_CUST_region
    Where rnk < = 100
  • Finally, materialized view NM2 may be incrementally refreshed according to the approaches described herein. A row-based log for materialized view NM1 tracks the updates, deletes, and inserts to materialized view NM1. Materialized view NM1 may be incrementally refreshed using techniques for incrementally refreshing summary tables, such as discussed in Lawande.
  • In general, a ranked materialized view should not include a group-by clause or a HAVING clause. The reason for this is that if rows for a group in the materialized view are changed in the base table and that particular group is not in the materialized view, then the base table would have to be examined to recompute the aggregate for the group at the time of incremental refresh. Since the base table is usually big, we want to avoid going to the base table as much as possible.
  • Hardware Overview
  • FIG. 6 is a block diagram that illustrates a computer system 600 upon which an embodiment of the invention may be implemented. Computer system 600 includes a bus 602 or other communication mechanism for communicating information, and a processor 604 coupled with bus 602 for processing information. Computer system 600 also includes a main memory 606, such as a random access memory (RAM) or other dynamic storage device, coupled to bus 602 for storing information and instructions to be executed by processor 604. Main memory 606 also may be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor 604. Computer system 600 further includes a read only memory (ROM) 608 or other static storage device coupled to bus 602 for storing static information and instructions for processor 604. A storage device 610, such as a magnetic disk or optical disk, is provided and coupled to bus 602 for storing information and instructions.
  • Computer system 600 may be coupled via bus 602 to a display 612, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device 614, including alphanumeric and other keys, is coupled to bus 602 for communicating information and command selections to processor 604. Another type of user input device is cursor control 616, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor 604 and for controlling cursor movement on display 612. This input device typically has two degrees of freedom in two axes, a first axis (e.g., x) and a second axis (e.g., y), that allows the device to specify positions in a plane.
  • The invention is related to the use of computer system 600 for implementing the techniques described herein. According to one embodiment of the invention, those techniques are performed by computer system 600 in response to processor 604 executing one or more sequences of one or more instructions contained in main memory 606. Such instructions may be read into main memory 606 from another computer-readable medium, such as storage device 610. Execution of the sequences of instructions contained in main memory 606 causes processor 604 to perform the process steps described herein. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions to implement the invention. Thus, embodiments of the invention are not limited to any specific combination of hardware circuitry and software.
  • The term “computer-readable medium” as used herein refers to any medium that participates in providing instructions to processor 604 for execution. Such a medium may take many forms, including but not limited to, non-volatile media, volatile media, and transmission media. Non-volatile media includes, for example, optical or magnetic disks, such as storage device 610. Volatile media includes dynamic memory, such as main memory 606. Transmission media includes coaxial cables, copper wire and fiber optics, including the wires that comprise bus 602. Transmission media can also take the form of acoustic or light waves, such as those generated during radio-wave and infra-red data communications.
  • Common forms of computer-readable media include, for example, a floppy disk, a flexible disk, hard disk, magnetic tape, or any other magnetic medium, a CD-ROM, any other optical medium, punchcards, papertape, any other physical medium with patterns of holes, a RAM, a PROM, and EPROM, a FLASH-EPROM, any other memory chip or cartridge, a carrier wave as described hereinafter, or any other medium from which a computer can read.
  • Various forms of computer readable media may be involved in carrying one or more sequences of one or more instructions to processor 604 for execution. For example, the instructions may initially be carried on a magnetic disk of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to computer system 600 can receive the data on the telephone line and use an infra-red transmitter to convert the data to an infra-red signal. An infra-red detector can receive the data carried in the infra-red signal and appropriate circuitry can place the data on bus 602. Bus 602 carries the data to main memory 606, from which processor 604 retrieves and executes the instructions. The instructions received by main memory 606 may optionally be stored on storage device 610 either before or after execution by processor 604.
  • Computer system 600 also includes a communication interface 618 coupled to bus 602. Communication interface 618 provides a two-way data communication coupling to a network link 620 that is connected to a local network 622. For example, communication interface 618 may be an integrated services digital network (ISDN) card or a modem to provide a data communication connection to a corresponding type of telephone line. As another example, communication interface 618 may be a local area network (LAN) card to provide a data communication connection to a compatible LAN. Wireless links may also be implemented. In any such implementation, communication interface 618 sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.
  • Network link 620 typically provides data communication through one or more networks to other data devices. For example, network link 620 may provide a connection through local network 622 to a host computer 624 or to data equipment operated by an Internet Service Provider (ISP) 626. ISP 626 in turn provides data communication services through the world wide packet data communication network now commonly referred to as the “Internet” 628. Local network 622 and Internet 628 both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link 620 and through communication interface 618, which carry the digital data to and from computer system 600, are exemplary forms of carrier waves transporting the information.
  • Computer system 600 can send messages and receive data, including program code, through the network(s), network link 620 and communication interface 618. In the Internet example, a server 630 might transmit a requested code for an application program through Internet 628, ISP 626, local network 622 and communication interface 618.
  • The received code may be executed by processor 604 as it is received, and/or stored in storage device 610, or other non-volatile storage for later execution. In this manner, computer system 600 may obtain application code in the form of a carrier wave.
  • In the foregoing specification, the invention has been described with reference to specific embodiments thereof. It will, however, be evident that various modifications and changes may be made thereto without departing from the broader spirit and scope of the invention. The specification and drawings are, accordingly, to be regarded in an illustrative rather than a restrictive sense.

Claims (14)

  1. 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. 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. 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.
  4. 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. 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. 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.
  7. 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.
  8. 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. 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. 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.
  11. 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. 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. 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.
  14. 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.
US11437072 2002-03-26 2006-05-19 Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view Active 2022-12-06 US7912834B2 (en)

Priority Applications (2)

Application Number Priority Date Filing Date Title
US10107106 US7111020B1 (en) 2002-03-26 2002-03-26 Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view
US11437072 US7912834B2 (en) 2002-03-26 2006-05-19 Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
US11437072 US7912834B2 (en) 2002-03-26 2006-05-19 Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view
US13005436 US8103689B2 (en) 2002-03-26 2011-01-12 Rewrite of queries containing rank or rownumber or min/max aggregate functions using a materialized view

Related Parent Applications (1)

Application Number Title Priority Date Filing Date
US10107106 Division US7111020B1 (en) 2002-03-26 2002-03-26 Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view

Related Child Applications (1)

Application Number Title Priority Date Filing Date
US13005436 Division US8103689B2 (en) 2002-03-26 2011-01-12 Rewrite of queries containing rank or rownumber or min/max aggregate functions using a materialized view

Publications (2)

Publication Number Publication Date
US20060212436A1 true true US20060212436A1 (en) 2006-09-21
US7912834B2 US7912834B2 (en) 2011-03-22

Family

ID=36974626

Family Applications (3)

Application Number Title Priority Date Filing Date
US10107106 Active 2022-07-05 US7111020B1 (en) 2002-03-26 2002-03-26 Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view
US11437072 Active 2022-12-06 US7912834B2 (en) 2002-03-26 2006-05-19 Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view
US13005436 Active US8103689B2 (en) 2002-03-26 2011-01-12 Rewrite of queries containing rank or rownumber or min/max aggregate functions using a materialized view

Family Applications Before (1)

Application Number Title Priority Date Filing Date
US10107106 Active 2022-07-05 US7111020B1 (en) 2002-03-26 2002-03-26 Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view

Family Applications After (1)

Application Number Title Priority Date Filing Date
US13005436 Active US8103689B2 (en) 2002-03-26 2011-01-12 Rewrite of queries containing rank or rownumber or min/max aggregate functions using a materialized view

Country Status (1)

Country Link
US (3) US7111020B1 (en)

Cited By (12)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20060253483A1 (en) * 2005-03-31 2006-11-09 Oracle International Corporation Method and mechanism of materialized view mix incremental refresh
US20060294064A1 (en) * 2005-06-24 2006-12-28 Microsoft Corporation Storing queries on devices with rewritable media
US20080065592A1 (en) * 2006-09-08 2008-03-13 Blade Makai Doyle Method, system and computer-readable media for software object relationship traversal for object-relational query binding
US20080086514A1 (en) * 2006-10-04 2008-04-10 Salesforce.Com, Inc. Methods and systems for providing fault recovery to side effects occurring during data processing
US20080306904A1 (en) * 2006-10-30 2008-12-11 Takeshi Fukuda System, method, and program product for integrating databases
US20080307011A1 (en) * 2007-06-07 2008-12-11 International Business Machines Corporation Failure recovery and error correction techniques for data loading in information warehouses
US20080313136A1 (en) * 2007-06-13 2008-12-18 International Business Machines Corporation Method and apparatus for producing up-to-date query results from tables including data from a data warehouse
US8548942B2 (en) 2006-10-04 2013-10-01 Salesforce.Com, Inc. Methods and systems for recursive saving of hierarchical objects to a database
US8682863B2 (en) 2006-10-04 2014-03-25 Salesforce.Com, Inc. Methods and systems for bulk row save logic in an object relational mapping layer and application framework
US20150088812A1 (en) * 2013-09-20 2015-03-26 Oracle International Corporation Maintaining staleness information for aggregate data
US20160098462A1 (en) * 2014-10-06 2016-04-07 Netapp, Inc. Enterprise Reporting Capabilities In Storage Management Systems
US9430530B1 (en) 2015-11-24 2016-08-30 International Business Machines Corporation Reusing database statistics for user aggregate queries

Families Citing this family (39)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7111020B1 (en) 2002-03-26 2006-09-19 Oracle International Corporation Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view
US7933867B1 (en) * 2003-11-12 2011-04-26 Teradata Us, Inc. Maintaining views of cube-based operations in a database system
US20050235001A1 (en) * 2004-03-31 2005-10-20 Nitzan Peleg Method and apparatus for refreshing materialized views
US7337164B2 (en) * 2004-03-31 2008-02-26 Sap Ag Fast search with very large result set
US20050234945A1 (en) * 2004-04-14 2005-10-20 Oracle International Corporation Allocating CPU resources for a particular refresh schedule
US8478742B2 (en) 2004-04-14 2013-07-02 Oracle Corporation Using estimated cost to refresh a set of materialized views (MVS)
US7890497B2 (en) * 2004-04-14 2011-02-15 Oracle International Corporation Using estimated cost to schedule an order for refreshing a set of materialized views (MVS)
US7734602B2 (en) * 2004-04-14 2010-06-08 Oracle International Corporation Choosing whether to use a delayed index maintenance depending on the portion of the materialized view (MV) changed
US8996502B2 (en) * 2004-05-17 2015-03-31 Oracle International Corporation Using join dependencies for refresh
US20050283458A1 (en) * 2004-06-22 2005-12-22 Microsoft Corporation Automatic detection of frequently used query patterns in a query workload
US7814042B2 (en) * 2004-08-17 2010-10-12 Oracle International Corporation Selecting candidate queries
US7930297B2 (en) * 2004-12-03 2011-04-19 Oracle International Corporation Materialized view maintenance and change tracking
US7577681B1 (en) * 2005-06-29 2009-08-18 Emc Corporation Methods and apparatus for managing contents of a database
US8386463B2 (en) * 2005-07-14 2013-02-26 International Business Machines Corporation Method and apparatus for dynamically associating different query execution strategies with selective portions of a database table
US8468152B2 (en) * 2005-08-04 2013-06-18 International Business Machines Corporation Autonomic refresh of a materialized query table in a computer database
US7814091B2 (en) * 2005-09-27 2010-10-12 Oracle International Corporation Multi-tiered query processing techniques for minus and intersect operators
US20070143245A1 (en) * 2005-12-16 2007-06-21 International Business Machines Corporation System and method for managing presentation of query results
US7451120B1 (en) * 2006-03-20 2008-11-11 Google Inc. Detecting novel document content
US8856083B2 (en) * 2006-06-09 2014-10-07 Oracle International Corporation Framework to optimize delete all row operations on database objects to improve throughput, query ability and flashback
US9430552B2 (en) * 2007-03-16 2016-08-30 Microsoft Technology Licensing, Llc View maintenance rules for an update pipeline of an object-relational mapping (ORM) platform
US7774354B2 (en) * 2007-08-02 2010-08-10 International Business Machines Corporation Method and system for response time optimization for task list retrieval from databases
US20090064160A1 (en) * 2007-08-31 2009-03-05 Microsoft Corporation Transparent lazy maintenance of indexes and materialized views
US8375073B1 (en) 2007-11-12 2013-02-12 Google Inc. Identification and ranking of news stories of interest
US8122033B2 (en) * 2008-01-09 2012-02-21 International Business Machines Corporation Database query optimization
US7996379B1 (en) 2008-02-01 2011-08-09 Google Inc. Document ranking using word relationships
US7921103B2 (en) * 2008-06-20 2011-04-05 Yahoo! Inc. Adaptive materialized view selection for databases
US8065269B2 (en) * 2008-12-19 2011-11-22 Ianywhere Solutions, Inc. Immediate maintenance of materialized views
US8346714B1 (en) * 2009-12-17 2013-01-01 Teradota Us, Inc. Transactiontime and validtime timestamping in an enterprise active data warehouse
US20120166400A1 (en) * 2010-12-28 2012-06-28 Teradata Us, Inc. Techniques for processing operations on column partitions in a database
US8799271B2 (en) * 2011-01-25 2014-08-05 Hewlett-Packard Development Company, L.P. Range predicate canonization for translating a query
US20130132352A1 (en) * 2011-11-23 2013-05-23 Microsoft Corporation Efficient fine-grained auditing for complex database queries
US8620924B2 (en) 2012-03-06 2013-12-31 International Business Machines Corporation Refreshing a full-text search index in a partitioned database
US9317554B2 (en) 2012-09-26 2016-04-19 Microsoft Technology Licensing, Llc SQL generation for assert, update and delete relational trees
US9454572B2 (en) 2013-03-14 2016-09-27 Oracle International Corporation Out-of-place materialized view refresh
US20140280028A1 (en) * 2013-03-14 2014-09-18 Oracle International Corporation Selective materialized view refresh
US20140330768A1 (en) * 2013-05-01 2014-11-06 Hewlett-Packard Development Company, L.P. Incrementally updated sample tables
US20150278288A1 (en) * 2014-03-27 2015-10-01 Salesforce.Com, Inc. Maintaining data consistency between transactional and non-transactional data stores
US20160350308A1 (en) * 2015-05-29 2016-12-01 Looker Data Sciences, Inc. Methods and systems for selectively retrieving data to provide a limited dataset for incorporation into a pivot table
US20170132276A1 (en) * 2015-10-15 2017-05-11 Sumo Logic Automatic partitioning

Citations (79)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US4773862A (en) * 1987-08-10 1988-09-27 Board Of Governors Of Wayne State University Method for mapping a joint venture and maps produced thereby
US5261098A (en) * 1991-08-28 1993-11-09 Sun Microsystems, Inc. Method and apparatus for deriving object type and obtaining object type attribute values
US5276870A (en) * 1987-12-11 1994-01-04 Hewlett-Packard Company View composition in a data base management system
US5404506A (en) * 1985-03-27 1995-04-04 Hitachi, Ltd. Knowledge based information retrieval system
US5418946A (en) * 1991-09-27 1995-05-23 Fuji Xerox Co., Ltd. Structured data classification device
US5444842A (en) * 1992-07-24 1995-08-22 Bentson; Sheridan Method and apparatus for displaying and updating structured information
US5548755A (en) * 1995-02-17 1996-08-20 International Business Machines Corporation System for optimizing correlated SQL queries in a relational database using magic decorrelation
US5551027A (en) * 1993-01-07 1996-08-27 International Business Machines Corporation Multi-tiered indexing method for partitioned data
US5560005A (en) * 1994-02-25 1996-09-24 Actamed Corp. Methods and systems for object-based relational distributed databases
US5581758A (en) * 1992-10-05 1996-12-03 International Business Machines Corporation Computer program product for object specification, generation, and management in a distributed database
US5584024A (en) * 1994-03-24 1996-12-10 Software Ag Interactive database query system and method for prohibiting the selection of semantically incorrect query parameters
US5594899A (en) * 1989-09-01 1997-01-14 Amdahl Corporation Operating system and data base having an access structure formed by a plurality of tables
US5598559A (en) * 1994-07-01 1997-01-28 Hewlett-Packard Company Method and apparatus for optimizing queries having group-by operators
US5600831A (en) * 1994-02-28 1997-02-04 Lucent Technologies Inc. Apparatus and methods for retrieving information by modifying query plan based on description of information sources
US5659728A (en) * 1994-12-30 1997-08-19 International Business Machines Corporation System and method for generating uniqueness information for optimizing an SQL query
US5701455A (en) * 1994-10-20 1997-12-23 International Business Machines Corporation Method and apparatus for reordering complex SQL queries using a modified generalized outer join operator
US5729730A (en) * 1995-03-28 1998-03-17 Dex Information Systems, Inc. Method and apparatus for improved information storage and retrieval system
US5734884A (en) * 1994-06-24 1998-03-31 International Business Machines Corporation Database execution cost and system performance estimator
US5765147A (en) * 1996-11-21 1998-06-09 International Business Machines Corportion Query rewrite for extended search capabilities
US5774692A (en) * 1995-10-05 1998-06-30 International Business Machines Corporation Outer quantifiers in object-oriented queries and views of database systems
US5778353A (en) * 1994-05-20 1998-07-07 International Business Machines Corporation Computer program product for optimizing data retrieval using index scanning
US5778355A (en) * 1996-06-11 1998-07-07 International Business Machines Corp. Database method and apparatus for interactively retrieving data members and related members from a collection of data
US5822751A (en) * 1996-12-16 1998-10-13 Microsoft Corporation Efficient multidimensional data aggregation operator implementation
US5826077A (en) * 1990-04-30 1998-10-20 Texas Instruments Incorporated Apparatus and method for adding an associative query capability to a programming language
US5832485A (en) * 1996-02-14 1998-11-03 Oracle Corporation Method for editing views created by joins
US5848405A (en) * 1997-04-21 1998-12-08 Oracle Corporation Method and apparatus for identifying new data by address ranges
US5873093A (en) * 1994-12-07 1999-02-16 Next Software, Inc. Method and apparatus for mapping objects to a data source
US5875447A (en) * 1996-05-30 1999-02-23 International Business Machines Corporation Reordering of complex SQL queries involving GROUPBYs, joins, outer joins and full outer joins
US5897632A (en) * 1996-08-27 1999-04-27 At&T Corp Method and system for using materialized views to evaluate queries involving aggregation
US5950210A (en) * 1996-01-29 1999-09-07 Nelson; Paul M. Database row version differentiation process
US5956706A (en) * 1997-05-09 1999-09-21 International Business Machines Corporation Method and system for limiting the cardinality of an SQL query result
US5960426A (en) * 1997-06-30 1999-09-28 International Business Machines Corporation Database system and method for supporting current of cursor updates and deletes from a select query from one or more updatable tables in single node and mpp environments
US5963933A (en) * 1997-06-25 1999-10-05 International Business Machines Corporation Efficient implementation of full outer join and anti-join
US5963959A (en) * 1997-05-30 1999-10-05 Oracle Corporation Fast refresh of snapshots
US5970482A (en) * 1996-02-12 1999-10-19 Datamind Corporation System for data mining using neuroagents
US5974407A (en) * 1997-09-29 1999-10-26 Sacks; Jerome E. Method and apparatus for implementing a hierarchical database management system (HDBMS) using a relational database management system (RDBMS) as the implementing apparatus
US5974416A (en) * 1997-11-10 1999-10-26 Microsoft Corporation Method of creating a tabular data stream for sending rows of data between client and server
US5978788A (en) * 1997-04-14 1999-11-02 International Business Machines Corporation System and method for generating multi-representations of a data cube
US5978791A (en) * 1995-04-11 1999-11-02 Kinetech, Inc. Data processing system using substantially unique identifiers to identify data items, whereby identical data items have the same identifiers
US5987455A (en) * 1997-06-30 1999-11-16 International Business Machines Corporation Intelligent compilation of procedural functions for query processing systems
US5991754A (en) * 1998-12-28 1999-11-23 Oracle Corporation Rewriting a query in terms of a summary based on aggregate computability and canonical format, and when a dimension table is on the child side of an outer join
US6006216A (en) * 1997-07-29 1999-12-21 Lucent Technologies Inc. Data architecture for fetch-intensive database applications
US6023695A (en) * 1997-10-31 2000-02-08 Oracle Corporation Summary table management in a computer system
US6026390A (en) * 1996-05-29 2000-02-15 At&T Corp Cost-based maintenance of materialized views
US6032144A (en) * 1996-05-29 2000-02-29 Lucent Technologies Inc. Optimization of queries using relational algebraic theta-semijoin operator
US6088705A (en) * 1997-07-02 2000-07-11 International Business Machines Corporation Method and apparatus for loading data into a database in a multiprocessor environment
US6108651A (en) * 1997-09-09 2000-08-22 Netscape Communications Corporation Heuristic co-identification of objects across heterogeneous information sources
US6125360A (en) * 1998-07-02 2000-09-26 Oracle Corporation Incremental maintenance of materialized views containing one-to-N lossless joins
US6134543A (en) * 1998-07-02 2000-10-17 Oracle Corporation Incremental maintenance of materialized views containing one-to-one lossless joins
US6199063B1 (en) * 1998-03-27 2001-03-06 Red Brick Systems, Inc. System and method for rewriting relational database queries
US6205451B1 (en) * 1998-05-22 2001-03-20 Oracle Corporation Method and apparatus for incremental refresh of summary tables in a database system
US6272502B1 (en) * 1998-05-11 2001-08-07 Lucent Technologies Inc. Refreshing materialized views of a database to maintain consistency with underlying data
US20010013030A1 (en) * 1998-03-27 2001-08-09 Informix Software, Inc. Defining and characterizing an analysis space for precomputed views
US6289335B1 (en) * 1997-06-23 2001-09-11 Oracle Corporation Fast refresh of snapshots containing subqueries
US6334128B1 (en) * 1998-12-28 2001-12-25 Oracle Corporation Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system
US6334114B1 (en) * 1997-10-31 2001-12-25 Oracle Corporation Method and apparatus for performing transactions in a stateless web environment which supports a declarative paradigm
US6339769B1 (en) * 1998-09-14 2002-01-15 International Business Machines Corporation Query optimization by transparently altering properties of relational tables using materialized views
US6356889B1 (en) * 1998-09-30 2002-03-12 International Business Machines Corporation Method for determining optimal database materializations using a query optimizer
US6356891B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Identifying indexes on materialized views for database workload
US6374263B1 (en) * 1999-07-19 2002-04-16 International Business Machines Corp. System for maintaining precomputed views
US6457020B1 (en) * 2000-03-20 2002-09-24 International Business Machines Corporation Query optimization using a multi-layered object cache
US6473201B1 (en) * 1998-03-27 2002-10-29 Oki Data Corporation Method and apparatus for analyzing and converting bi-level image data
US6477525B1 (en) * 1998-12-28 2002-11-05 Oracle Corporation Rewriting a query in terms of a summary based on one-to-one and one-to-many losslessness of joins
US6484159B1 (en) * 1999-05-20 2002-11-19 At&T Corp. Method and system for incremental database maintenance
US6493701B2 (en) * 2000-11-22 2002-12-10 Sybase, Inc. Database system with methodogy providing faster N-ary nested loop joins
US6496828B1 (en) * 1999-12-17 2002-12-17 International Business Machines Corporation Support for summary tables in a heterogeneous database environment
US6546402B1 (en) * 2000-06-05 2003-04-08 International Business Machines Corporation System and method for asynchronous view maintenance
US6560476B1 (en) * 1999-11-01 2003-05-06 Arthrovision, Inc. Evaluating disease progression using magnetic resonance imaging
US20030093415A1 (en) * 2001-11-15 2003-05-15 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US20030135480A1 (en) * 2002-01-14 2003-07-17 Van Arsdale Robert S. System for updating a database
US20030159136A1 (en) * 2001-09-28 2003-08-21 Huang Xiao Fei Method and system for server synchronization with a computing device
US20030200218A1 (en) * 2002-04-23 2003-10-23 International Business Machines Corporation Content management system and methodology featuring query conversion capability for efficient searching
US6741997B1 (en) * 2001-06-14 2004-05-25 Oracle International Corporation Instantiating objects in distributed database systems
US6748392B1 (en) * 2001-03-06 2004-06-08 Microsoft Corporation System and method for segmented evaluation of database queries
US20040122828A1 (en) * 2002-12-23 2004-06-24 Sidle Richard S. Independent deferred incremental refresh of materialized views
US6763352B2 (en) * 1999-05-21 2004-07-13 International Business Machines Corporation Incremental maintenance of summary tables with complex grouping expressions
US20050055382A1 (en) * 2000-06-28 2005-03-10 Lounas Ferrat Universal synchronization
US6882993B1 (en) * 2002-01-28 2005-04-19 Oracle International Corporation Incremental refresh of materialized views with joins and aggregates after arbitrary DML operations to multiple tables
US7007006B2 (en) * 2001-06-21 2006-02-28 International Business Machines Corporation Method for recommending indexes and materialized views for a database workload

Family Cites Families (9)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US8700458B2 (en) 1989-05-01 2014-04-15 Catalina Marketing Corporation System, method, and database for processing transactions
JP2721940B2 (en) 1991-10-17 1998-03-04 日野自動車工業株式会社 Limited slip differential
US6496819B1 (en) 1998-12-28 2002-12-17 Oracle Corporation Rewriting a query in terms of a summary based on functional dependencies and join backs, and based on join derivability
US6826597B1 (en) 1999-03-17 2004-11-30 Oracle International Corporation Providing clients with services that retrieve data from data sources that do not necessarily support the format required by the clients
US6513029B1 (en) * 2000-04-20 2003-01-28 Microsoft Corporation Interesting table-subset selection for database workload materialized view selection
US7162467B2 (en) 2001-02-22 2007-01-09 Greenplum, Inc. Systems and methods for managing distributed database resources
US7158994B1 (en) 2001-09-28 2007-01-02 Oracle International Corporation Object-oriented materialized views
US6708179B1 (en) 2001-09-28 2004-03-16 Oracle International Corporation Incremental refresh of materialized views for many-to-many relationships
US7111020B1 (en) 2002-03-26 2006-09-19 Oracle International Corporation Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view

Patent Citations (86)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5404506A (en) * 1985-03-27 1995-04-04 Hitachi, Ltd. Knowledge based information retrieval system
US4773862A (en) * 1987-08-10 1988-09-27 Board Of Governors Of Wayne State University Method for mapping a joint venture and maps produced thereby
US5276870A (en) * 1987-12-11 1994-01-04 Hewlett-Packard Company View composition in a data base management system
US5594899A (en) * 1989-09-01 1997-01-14 Amdahl Corporation Operating system and data base having an access structure formed by a plurality of tables
US5826077A (en) * 1990-04-30 1998-10-20 Texas Instruments Incorporated Apparatus and method for adding an associative query capability to a programming language
US5261098A (en) * 1991-08-28 1993-11-09 Sun Microsystems, Inc. Method and apparatus for deriving object type and obtaining object type attribute values
US5418946A (en) * 1991-09-27 1995-05-23 Fuji Xerox Co., Ltd. Structured data classification device
US5444842A (en) * 1992-07-24 1995-08-22 Bentson; Sheridan Method and apparatus for displaying and updating structured information
US5581758A (en) * 1992-10-05 1996-12-03 International Business Machines Corporation Computer program product for object specification, generation, and management in a distributed database
US5551027A (en) * 1993-01-07 1996-08-27 International Business Machines Corporation Multi-tiered indexing method for partitioned data
US5724575A (en) * 1994-02-25 1998-03-03 Actamed Corp. Method and system for object-based relational distributed databases
US5560005A (en) * 1994-02-25 1996-09-24 Actamed Corp. Methods and systems for object-based relational distributed databases
US5600831A (en) * 1994-02-28 1997-02-04 Lucent Technologies Inc. Apparatus and methods for retrieving information by modifying query plan based on description of information sources
US5584024A (en) * 1994-03-24 1996-12-10 Software Ag Interactive database query system and method for prohibiting the selection of semantically incorrect query parameters
US5812840A (en) * 1994-03-24 1998-09-22 Speedware Ltee./Ltd. Database query system
US5778353A (en) * 1994-05-20 1998-07-07 International Business Machines Corporation Computer program product for optimizing data retrieval using index scanning
US5734884A (en) * 1994-06-24 1998-03-31 International Business Machines Corporation Database execution cost and system performance estimator
US6003022A (en) * 1994-06-24 1999-12-14 International Buisness Machines Corporation Database execution cost and system performance estimator
US5598559A (en) * 1994-07-01 1997-01-28 Hewlett-Packard Company Method and apparatus for optimizing queries having group-by operators
US5701455A (en) * 1994-10-20 1997-12-23 International Business Machines Corporation Method and apparatus for reordering complex SQL queries using a modified generalized outer join operator
US5873093A (en) * 1994-12-07 1999-02-16 Next Software, Inc. Method and apparatus for mapping objects to a data source
US5659728A (en) * 1994-12-30 1997-08-19 International Business Machines Corporation System and method for generating uniqueness information for optimizing an SQL query
US5548755A (en) * 1995-02-17 1996-08-20 International Business Machines Corporation System for optimizing correlated SQL queries in a relational database using magic decorrelation
US5729730A (en) * 1995-03-28 1998-03-17 Dex Information Systems, Inc. Method and apparatus for improved information storage and retrieval system
US5978791A (en) * 1995-04-11 1999-11-02 Kinetech, Inc. Data processing system using substantially unique identifiers to identify data items, whereby identical data items have the same identifiers
US5774692A (en) * 1995-10-05 1998-06-30 International Business Machines Corporation Outer quantifiers in object-oriented queries and views of database systems
US5950210A (en) * 1996-01-29 1999-09-07 Nelson; Paul M. Database row version differentiation process
US5970482A (en) * 1996-02-12 1999-10-19 Datamind Corporation System for data mining using neuroagents
US5832485A (en) * 1996-02-14 1998-11-03 Oracle Corporation Method for editing views created by joins
US6032144A (en) * 1996-05-29 2000-02-29 Lucent Technologies Inc. Optimization of queries using relational algebraic theta-semijoin operator
US6026390A (en) * 1996-05-29 2000-02-15 At&T Corp Cost-based maintenance of materialized views
US5960427A (en) * 1996-05-30 1999-09-28 International Business Machines Corporation Reordering of complex SQL queries involving groupbys, joins, outer joins and full outer joins
US5875447A (en) * 1996-05-30 1999-02-23 International Business Machines Corporation Reordering of complex SQL queries involving GROUPBYs, joins, outer joins and full outer joins
US5778355A (en) * 1996-06-11 1998-07-07 International Business Machines Corp. Database method and apparatus for interactively retrieving data members and related members from a collection of data
US5897632A (en) * 1996-08-27 1999-04-27 At&T Corp Method and system for using materialized views to evaluate queries involving aggregation
US5765147A (en) * 1996-11-21 1998-06-09 International Business Machines Corportion Query rewrite for extended search capabilities
US5822751A (en) * 1996-12-16 1998-10-13 Microsoft Corporation Efficient multidimensional data aggregation operator implementation
US5978788A (en) * 1997-04-14 1999-11-02 International Business Machines Corporation System and method for generating multi-representations of a data cube
US5848405A (en) * 1997-04-21 1998-12-08 Oracle Corporation Method and apparatus for identifying new data by address ranges
US5956706A (en) * 1997-05-09 1999-09-21 International Business Machines Corporation Method and system for limiting the cardinality of an SQL query result
US5963959A (en) * 1997-05-30 1999-10-05 Oracle Corporation Fast refresh of snapshots
US6289335B1 (en) * 1997-06-23 2001-09-11 Oracle Corporation Fast refresh of snapshots containing subqueries
US5963933A (en) * 1997-06-25 1999-10-05 International Business Machines Corporation Efficient implementation of full outer join and anti-join
US5960426A (en) * 1997-06-30 1999-09-28 International Business Machines Corporation Database system and method for supporting current of cursor updates and deletes from a select query from one or more updatable tables in single node and mpp environments
US5987455A (en) * 1997-06-30 1999-11-16 International Business Machines Corporation Intelligent compilation of procedural functions for query processing systems
US6088705A (en) * 1997-07-02 2000-07-11 International Business Machines Corporation Method and apparatus for loading data into a database in a multiprocessor environment
US6006216A (en) * 1997-07-29 1999-12-21 Lucent Technologies Inc. Data architecture for fetch-intensive database applications
US6108651A (en) * 1997-09-09 2000-08-22 Netscape Communications Corporation Heuristic co-identification of objects across heterogeneous information sources
US5974407A (en) * 1997-09-29 1999-10-26 Sacks; Jerome E. Method and apparatus for implementing a hierarchical database management system (HDBMS) using a relational database management system (RDBMS) as the implementing apparatus
US6023695A (en) * 1997-10-31 2000-02-08 Oracle Corporation Summary table management in a computer system
US6334114B1 (en) * 1997-10-31 2001-12-25 Oracle Corporation Method and apparatus for performing transactions in a stateless web environment which supports a declarative paradigm
US5974416A (en) * 1997-11-10 1999-10-26 Microsoft Corporation Method of creating a tabular data stream for sending rows of data between client and server
US20010013030A1 (en) * 1998-03-27 2001-08-09 Informix Software, Inc. Defining and characterizing an analysis space for precomputed views
US6629094B1 (en) * 1998-03-27 2003-09-30 International Business Machines Corporation System and method for rewriting relational database queries
US6473201B1 (en) * 1998-03-27 2002-10-29 Oki Data Corporation Method and apparatus for analyzing and converting bi-level image data
US6493699B2 (en) * 1998-03-27 2002-12-10 International Business Machines Corporation Defining and characterizing an analysis space for precomputed views
US6480836B1 (en) * 1998-03-27 2002-11-12 International Business Machines Corporation System and method for determining and generating candidate views for a database
US6199063B1 (en) * 1998-03-27 2001-03-06 Red Brick Systems, Inc. System and method for rewriting relational database queries
US6272502B1 (en) * 1998-05-11 2001-08-07 Lucent Technologies Inc. Refreshing materialized views of a database to maintain consistency with underlying data
US6205451B1 (en) * 1998-05-22 2001-03-20 Oracle Corporation Method and apparatus for incremental refresh of summary tables in a database system
US6134543A (en) * 1998-07-02 2000-10-17 Oracle Corporation Incremental maintenance of materialized views containing one-to-one lossless joins
US6125360A (en) * 1998-07-02 2000-09-26 Oracle Corporation Incremental maintenance of materialized views containing one-to-N lossless joins
US6339769B1 (en) * 1998-09-14 2002-01-15 International Business Machines Corporation Query optimization by transparently altering properties of relational tables using materialized views
US6356889B1 (en) * 1998-09-30 2002-03-12 International Business Machines Corporation Method for determining optimal database materializations using a query optimizer
US6334128B1 (en) * 1998-12-28 2001-12-25 Oracle Corporation Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system
US5991754A (en) * 1998-12-28 1999-11-23 Oracle Corporation Rewriting a query in terms of a summary based on aggregate computability and canonical format, and when a dimension table is on the child side of an outer join
US6477525B1 (en) * 1998-12-28 2002-11-05 Oracle Corporation Rewriting a query in terms of a summary based on one-to-one and one-to-many losslessness of joins
US6484159B1 (en) * 1999-05-20 2002-11-19 At&T Corp. Method and system for incremental database maintenance
US6763352B2 (en) * 1999-05-21 2004-07-13 International Business Machines Corporation Incremental maintenance of summary tables with complex grouping expressions
US6374263B1 (en) * 1999-07-19 2002-04-16 International Business Machines Corp. System for maintaining precomputed views
US6560476B1 (en) * 1999-11-01 2003-05-06 Arthrovision, Inc. Evaluating disease progression using magnetic resonance imaging
US6496828B1 (en) * 1999-12-17 2002-12-17 International Business Machines Corporation Support for summary tables in a heterogeneous database environment
US6457020B1 (en) * 2000-03-20 2002-09-24 International Business Machines Corporation Query optimization using a multi-layered object cache
US6356891B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Identifying indexes on materialized views for database workload
US6546402B1 (en) * 2000-06-05 2003-04-08 International Business Machines Corporation System and method for asynchronous view maintenance
US20050055382A1 (en) * 2000-06-28 2005-03-10 Lounas Ferrat Universal synchronization
US6493701B2 (en) * 2000-11-22 2002-12-10 Sybase, Inc. Database system with methodogy providing faster N-ary nested loop joins
US6748392B1 (en) * 2001-03-06 2004-06-08 Microsoft Corporation System and method for segmented evaluation of database queries
US6741997B1 (en) * 2001-06-14 2004-05-25 Oracle International Corporation Instantiating objects in distributed database systems
US7007006B2 (en) * 2001-06-21 2006-02-28 International Business Machines Corporation Method for recommending indexes and materialized views for a database workload
US20030159136A1 (en) * 2001-09-28 2003-08-21 Huang Xiao Fei Method and system for server synchronization with a computing device
US20030093415A1 (en) * 2001-11-15 2003-05-15 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US20030135480A1 (en) * 2002-01-14 2003-07-17 Van Arsdale Robert S. System for updating a database
US6882993B1 (en) * 2002-01-28 2005-04-19 Oracle International Corporation Incremental refresh of materialized views with joins and aggregates after arbitrary DML operations to multiple tables
US20030200218A1 (en) * 2002-04-23 2003-10-23 International Business Machines Corporation Content management system and methodology featuring query conversion capability for efficient searching
US20040122828A1 (en) * 2002-12-23 2004-06-24 Sidle Richard S. Independent deferred incremental refresh of materialized views

Cited By (24)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7895186B2 (en) * 2005-03-31 2011-02-22 Oracle International Corp. Method and mechanism of materialized view mix incremental refresh
US20060253483A1 (en) * 2005-03-31 2006-11-09 Oracle International Corporation Method and mechanism of materialized view mix incremental refresh
US20060294064A1 (en) * 2005-06-24 2006-12-28 Microsoft Corporation Storing queries on devices with rewritable media
WO2007001608A2 (en) * 2005-06-24 2007-01-04 Microsoft Corporation Storing queries on devices with rewritable media
WO2007001608A3 (en) * 2005-06-24 2007-11-22 Microsoft Corp Storing queries on devices with rewritable media
US20080065592A1 (en) * 2006-09-08 2008-03-13 Blade Makai Doyle Method, system and computer-readable media for software object relationship traversal for object-relational query binding
US8527502B2 (en) 2006-09-08 2013-09-03 Blade Makai Doyle Method, system and computer-readable media for software object relationship traversal for object-relational query binding
US8930322B2 (en) 2006-10-04 2015-01-06 Salesforce.Com, Inc. Methods and systems for bulk row save logic in an object relational mapping layer and application framework
US8682863B2 (en) 2006-10-04 2014-03-25 Salesforce.Com, Inc. Methods and systems for bulk row save logic in an object relational mapping layer and application framework
US8918361B2 (en) 2006-10-04 2014-12-23 Salesforce.Com, Inc. Methods and systems for recursive saving of hierarchical objects to a database
US8161010B2 (en) * 2006-10-04 2012-04-17 Salesforce.Com, Inc. Methods and systems for providing fault recovery to side effects occurring during data processing
US20080086514A1 (en) * 2006-10-04 2008-04-10 Salesforce.Com, Inc. Methods and systems for providing fault recovery to side effects occurring during data processing
US8548942B2 (en) 2006-10-04 2013-10-01 Salesforce.Com, Inc. Methods and systems for recursive saving of hierarchical objects to a database
US8548952B2 (en) 2006-10-04 2013-10-01 Salesforce.Com, Inc. Methods and systems for providing fault recovery to side effects occurring during data processing
US20080306904A1 (en) * 2006-10-30 2008-12-11 Takeshi Fukuda System, method, and program product for integrating databases
US8965912B2 (en) 2006-10-30 2015-02-24 International Business Machines Corporation Integrating databases
US20080307011A1 (en) * 2007-06-07 2008-12-11 International Business Machines Corporation Failure recovery and error correction techniques for data loading in information warehouses
US9218377B2 (en) * 2007-06-07 2015-12-22 International Business Machines Corporation Failure recovery and error correction techniques for data loading in information warehouses
US20080313136A1 (en) * 2007-06-13 2008-12-18 International Business Machines Corporation Method and apparatus for producing up-to-date query results from tables including data from a data warehouse
US20150088812A1 (en) * 2013-09-20 2015-03-26 Oracle International Corporation Maintaining staleness information for aggregate data
US9659039B2 (en) * 2013-09-20 2017-05-23 Oracle International Corporation Maintaining staleness information for aggregate data
US20160098462A1 (en) * 2014-10-06 2016-04-07 Netapp, Inc. Enterprise Reporting Capabilities In Storage Management Systems
US9690832B2 (en) * 2014-10-06 2017-06-27 Netapp, Inc. Enterprise reporting capabilities in storage management systems
US9430530B1 (en) 2015-11-24 2016-08-30 International Business Machines Corporation Reusing database statistics for user aggregate queries

Also Published As

Publication number Publication date Type
US7912834B2 (en) 2011-03-22 grant
US8103689B2 (en) 2012-01-24 grant
US7111020B1 (en) 2006-09-19 grant
US20110106790A1 (en) 2011-05-05 application

Similar Documents

Publication Publication Date Title
Vassiliadis Modeling multidimensional databases, cubes and cube operations
Gupta et al. Maintenance of materialized views: Problems, techniques, and applications
Harinarayan et al. Implementing data cubes efficiently
US7127467B2 (en) Managing expressions in a database system
US6081799A (en) Executing complex SQL queries using index screening for conjunct or disjunct index operations
Bertino et al. Indexing techniques for queries on nested objects
US6622138B1 (en) Method and apparatus for optimizing computation of OLAP ranking functions
US6285997B1 (en) Query optimization with deferred update and autonomous sources
US5706494A (en) System and method for constraint checking bulk data in a database
Hanson A performance analysis of view materialization strategies
US5926813A (en) Database system index selection using cost evaluation of a workload for multiple candidate index configurations
US6732096B1 (en) Optimizing an aggregate join query
US6374232B1 (en) Method and mechanism for retrieving values from a database
US6266663B1 (en) User-defined search using index exploitation
US6847978B2 (en) Automatic database statistics creation
Cui et al. Tracing the lineage of view data in a warehousing environment
US6684203B1 (en) Using global temporary tables to transform queries
US6128610A (en) Index with entries that store the key of a row and all non-key values of the row
US6505189B1 (en) Aggregate join index for relational databases
US5913207A (en) Database system index selection using index configuration enumeration for a workload
US6374235B1 (en) Method, system, and program for a join operation on a multi-column table and satellite tables including duplicate values
US6775682B1 (en) Evaluation of rollups with distinct aggregates by using sequence of sorts and partitioning by measures
US5960426A (en) Database system and method for supporting current of cursor updates and deletes from a select query from one or more updatable tables in single node and mpp environments
US5440730A (en) Time index access structure for temporal databases having concurrent multiple versions
US7272591B1 (en) Method and system for updating value correlation optimizations

Legal Events

Date Code Title Description
FPAY Fee payment

Year of fee payment: 4

CC Certificate of correction