Method and mechanism of materialized view mix incremental refresh

CROSS-REFERENCE TO RELATED APPLICATIONS

This application claims priority from U.S. Provisional Patent Application No. 60/667,183 filed on Mar. 31, 2005.

BACKGROUND OF THE INVENTION

1. Field of the Invention

The present invention generally relates to materialized views and, more particularly, to the incremental refresh of the materialized view.

2. Brief Description of Related Developments

The materialized view is an Oracle object that records valuable summary data in the data warehouse environment. Extracting summary data from a data warehouse often requires a time-consuming and expensive process through complex queries with join and/or aggregate constructs. As more periodical changes made to the tables in the data warehouse, maintaining summary data in the materialized views becomes an increasingly important topic. It is not practical to always rebuild the materialized views from scratch (i.e., complete refresh) due to the expensive nature.

The materialized view maintains pre-calculated data resulting from highly aggregated/joined queries. It has been increasingly and widely used in the data warehouse application to achieve high-performance query processing in accessing huge amount of historical data. Its values can be seen in the two aspects as query rewrite and incremental refresh. The query rewrite mechanism achieves better query processing performance by rewriting the given query in terms of the materialized view. The response time is thus shortened by using pre-calculated results in the materialized view. On the other hand, the incremental refresh provides a faster mechanism to synchronize the data between the base tables and the materialized view.

In the past, the materialized view was “atomic and treated as an unseparatable object so that the status of its data can only be either globally fresh or global stale. It is a serious drawback that affects the availability of the materialized view. For example, when any of the base tables of the materialized view gets updated, the materialized view immediately becomes globally “stale”. All the data in the materialized view becomes distrusted and unusable in the query rewrite (unless lowering the confidence level) since there is no easy way to identity which part of the data gets affected by the changes. Later, with the partition change tracking technique, the staleness of the materialized view is separated at the partition level.

In the materialized view maintenance, the row-based incremental refresh (using log with changed data rows) was the only data synchronization approach without needing to recalculate the data of the materialized view (i.e., complete refresh). However, the row-change-based incremental refresh has its limitations. For example, when the base table is partitioned (e.g., by range) and a partition maintenance operation (e.g., exchange partitions) occurs, the existing row-based incremental refresh is unable to handle such partition change. As a result, the only way to bring the materialized view back to the fresh state is through the complete refresh that is very expensive especially with a large amount of data. It is noted that the use of data partitioning becomes even more popular and is increasingly adopted in the OLAP system and very suitable for managing historical data. Lacking the ability of incrementally refreshing the materialized view after partition maintenance operations is a serious disadvantage.

The incremental refresh approach to materialized view maintenance incrementally applies the changes in the tables of the data warehouse to refresh the materialized views (i.e., incremental refresh). To enable the incremental refresh, catching change data made through various operations including conventional operations (e.g., INSERT, UPDATE and DELETE), direct path load and PMOP (partition maintenance operation) is the fundamental step. To catch conventional DML operations to the base table of the materialized view, a materialized view log is created and associated with the base table. When the base table is changed by conventional DML operations, the information of changed rows is recorded in the materialized view log. During the incremental refresh of the materialized view, the change data recorded in the materialized view log is used to determine what changes need to be made to the materialized view. Therefore, the materialized view log has been a mandatory requirement for the materialized view supporting incremental refresh. Without the materialized view log, the log-based incremental refresh is not possible.

As mentioned previously, a partition-based (“PCT”) incremental refresh approach was proposed to refresh the materialized view using partition change information and partition change tracking. The refresh algorithm for the partition-based incremental refresh does not require the materialized view log if the materialized view meets PCT (Partitioning Change Tracking) requirements and only PCT base tables are updated. However, the PCT incremental refresh cannot handle the case when the non-partitioned base table changes. In the general case when both non-partitioned and partitioned base tables are changed and partitioned base tables have partition maintenance operations, no existing incremental refresh method is available to use. Also, the current log-based (FAST) refresh method heavily relies on the materialized view logs of all base tables, but recording and maintaining those logs cause huge processing overheads during DML operations and materialized view refresh, respectively. Removing the dependency on the materialized view log is a very difficult refresh issue.

SUMMARY OF THE INVENTION

The present invention is directed to a method and system for updating both partition change track tables and non-partition track tables in a materialized view. In one embodiment the method includes performing a log-based incremental refresh on the materialized view and performing a partition change tracking based refresh on the materialized view. Dependency on the materialized view log for the PCT base table is eliminated and not required.

In another aspect, the present invention is directed to the incremental maintenance of a materialized view. In one embodiment, a method includes applying row changes from non-PCT base tables to the materialized view and applying partition changes from the PCT base tables to the materialized view.

In a further aspect, the present invention is directed to a method of fast incremental refresh of a materialized view. In one embodiment the method includes creating a materialized view from one or more base tables including at least one partitioned PCT table and at least one non-partitioned base table. The partition keys of the at least one partitioned PCT base table are stored on a defining query of the materialized view. Materialized view logs are created for all non-partitioned tables and any row changes from the at least one non-PCT base table are applied to the materialized view. Partition changes from the at least one PCT base table are also applied to the materialized view.

BRIEF DESCRIPTION OF THE DRAWINGS

The foregoing aspects and other features of the present invention are explained in the following description, taken in connection with the accompanying drawings, wherein:

<figref idrefs="DRAWINGS">FIG. 1</figref> is an illustration of one embodiment of a first phase of a mix incremental refresh process incorporating features of the present invention.

<figref idrefs="DRAWINGS">FIG. 2</figref> is an illustration of one embodiment of a second phase of a mix incremental refresh process incorporating features of the present invention.

<figref idrefs="DRAWINGS">FIG. 3</figref> is a flowchart of one embodiment of a mix incremental refresh process incorporating features of the present invention.

<figref idrefs="DRAWINGS">FIG. 4</figref> is a block diagram of one embodiment of a typical apparatus that can be used to practice the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT(s)

Referring to <figref idrefs="DRAWINGS">FIG. 1</figref>, one embodiment of a process incorporating features of the present invention is illustrated. Although the present invention will be described with reference to the embodiment shown in the drawings, it should be understood that the present invention can be embodied in many alternate forms of embodiments. In addition, any suitable size, shape or type of elements or materials could be used.

The disclosed embodiments provide an incremental refresh of the materialized view based on a mixed approach of incremental refresh, referred to herein as a MIX refresh, when both the PCT base table and non-PCT base table of the materialized view get changed. For example, a partition maintenance operation (PMOP) such as exchange partition can be done on a PCT base table and DML changes can be done on a non-PCT base table. The disclosed embodiments provide an incremental refresh solution that supports the general update scenario where both PCT and non-PCT tables are updated with any types of changes (e.g., conventional DMLs, direct load insert and partition maintenance operations). Previously, the only method to refresh the materialized view is through complete refresh (re-compute the data from scratch) which is very expensive and is virtually impractical to use when the base tables are large.

The disclosed embodiments also remove the dependency on the materialized view log for the PCT base table and provide a log-less incremental refresh on the PCT base table. The materialized view that has PCT base tables no longer requires the materialized view logs on those PCT base tables. There are many advantages to remove the dependency on the materialized view log for those PCT tables. First of all, it eliminates the storage and the change logging overhead for the materialized log when a PCT base table gets updated. Second, since there is no need for the materialized view log, the incremental refresh does not need to pay the materialized view log maintenance cost (e.g. log recording/setup/purging) for those PCT base tables. Currently, the materialized view log maintenance during refresh has caused a big performance overhead and slowed down the refresh execution. Without the materialized view log on the PCT table, the DML on that table can be executed faster by avoiding change recording overhead.

Referring to <figref idrefs="DRAWINGS">FIGS. 1 and 2</figref>, the disclosed embodiments combine the conventional log-based incremental refresh and the partition change tracking (PCT) based refresh approaches and generally includes two phases. The first phase, referring to <figref idrefs="DRAWINGS">FIG. 1</figref>, is to apply the changes of non-PCT base tables (using materialized view logs and/or direct load log) to the materialized view. The second phase, referring to FIG. <b>2</b>, includes applying the changes of PCT base tables (using partition change information such as partition range values and partition system change numbers (“SCN”) changes, to the materialized view.

Referring to <figref idrefs="DRAWINGS">FIG. 1</figref>, one example of a materialized aggregate view is shown to illustrate how the MIX refresh works. Four base tables, identified as FACT <b>101</b>, STORE <b>102</b>, TIME <b>103</b> and PROMOTION <b>104</b>, are referenced by the materialized view, SUMMARY_MAV. Among the four base tables, FACT <b>101</b> and STORE <b>102</b> are partitioned PCT tables, while TIME <b>103</b> and PROMOTION <b>104</b> are regular, non-partitioned tables. As illustrated in <figref idrefs="DRAWINGS">FIG. 1</figref>, the areas <b>110</b>, <b>111</b> and <b>112</b> of the FACT table <b>101</b> and the area <b>120</b> in the STORE table <b>102</b> represent the changed partitions. The area <b>130</b> in the TIME table <b>103</b> and the area <b>140</b> in the PROMOTION table <b>104</b> represent the changed rows. The general specifications of the tables <b>101</b>-<b>104</b> can be indicated as follows:

<tables id="TABLE-US-00001" num="00001"><table frame="none" colsep="0" rowsep="0" pgwide="1"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="273pt" align="center" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row><row><entry>FACT</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="7"><colspec colname="1" colwidth="42pt" align="left" /><colspec colname="2" colwidth="35pt" align="left" /><colspec colname="3" colwidth="28pt" align="left" /><colspec colname="4" colwidth="49pt" align="left" /><colspec colname="5" colwidth="49pt" align="left" /><colspec colname="6" colwidth="35pt" align="left" /><colspec colname="7" colwidth="35pt" align="left" /><tbody valign="top"><row><entry>TIME_KEY</entry><entry>MONTH</entry><entry>WEEK</entry><entry>STORE KEY</entry><entry>PROMOTION</entry><entry>DOLLAR</entry><entry>DOLLAR</entry></row><row><entry /><entry /><entry /><entry /><entry>KEY</entry><entry>SALES</entry><entry>COST</entry></row><row><entry namest="1" nameend="7" align="center" rowsep="1" /></row><row><entry namest="1" nameend="7" align="left" id="FOO-00001">(List Partitioned by WEEK)</entry></row></tbody></tgroup></table></tables>

<tables id="TABLE-US-00002" num="00002"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="center" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row><row><entry>STORE</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="4"><colspec colname="offset" colwidth="21pt" align="left" /><colspec colname="1" colwidth="63pt" align="left" /><colspec colname="2" colwidth="77pt" align="left" /><colspec colname="3" colwidth="56pt" align="left" /><tbody valign="top"><row><entry /><entry>STORE_KEY</entry><entry>STORE_NAME</entry><entry>REGION</entry></row><row><entry /><entry namest="offset" nameend="3" align="center" rowsep="1" /></row><row><entry /><entry namest="offset" nameend="3" align="left" id="FOO-00002">(Range Partitioned by STORE_KEY)</entry></row></tbody></tgroup></table></tables>

<tables id="TABLE-US-00003" num="00003"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="center" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row><row><entry>TIME</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="1" colwidth="42pt" align="left" /><colspec colname="2" colwidth="35pt" align="left" /><colspec colname="3" colwidth="28pt" align="left" /><colspec colname="4" colwidth="28pt" align="left" /><colspec colname="5" colwidth="35pt" align="left" /><colspec colname="6" colwidth="49pt" align="left" /><tbody valign="top"><row><entry>TIME_KEY</entry><entry>MONTH</entry><entry>YEAR</entry><entry>WEEK</entry><entry>QUAR-</entry><entry>FISCAL_QTR</entry></row><row><entry /><entry /><entry /><entry /><entry>TER</entry></row><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

<tables id="TABLE-US-00004" num="00004"><table frame="none" colsep="0" rowsep="0" pgwide="1"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="301pt" align="center" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row><row><entry>PROMOTION</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="4"><colspec colname="1" colwidth="70pt" align="left" /><colspec colname="2" colwidth="70pt" align="left" /><colspec colname="3" colwidth="84pt" align="left" /><colspec colname="4" colwidth="77pt" align="left" /><tbody valign="top"><row><entry>PROMOTION_KEY</entry><entry>PROMOTION_COST</entry><entry>PROMO_START_DATE</entry><entry>PROMO_END_DATE</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

The materialized view, SUMMARY_MAV, is created with the following CREATE MATERIALIZED VIEW statement:

<tables id="TABLE-US-00005" num="00005"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="left" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry>CREATE MATERIALIZED VIEW summary_mav</entry></row><row><entry> BUILD IMMEDIATE</entry></row><row><entry> REFRESH FAST ON DEMAND</entry></row><row><entry> AS</entry></row><row><entry> SELECT pf.week, s.store_key, s.store_name, p.promotion_key,</entry></row><row><entry>     AVG(pf.dollar_sales) as avg_sales,</entry></row><row><entry>     COUNT(*) as cnt, COUNT(pf.dollar_sales) as cnt_dsales</entry></row><row><entry> FROM fact pf, store s, promotion p, time t</entry></row><row><entry> WHERE s.store_key = pf.store_key and</entry></row><row><entry>     pf.time_key = t.time_key and</entry></row><row><entry>     pf.promotion_key = p.promotion_key</entry></row><row><entry>     GROUP BY pf.week, s.store_key, s.store_name,</entry></row><row><entry>     p.promotion_key;</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

It is noted that in the above CREATE MATERIALIZED VIEW statement both partition keys of tables FACT and STORE are in the materialized view's defining query and meet PCT requirements. As a result, both tables (FAST and STORE) do not need materialized view logs (as claimed in this invention). The other two base tables (TIME and PROMOTION) are regular base tables having materialized view logs created on them. That is, the materialized view only needs materialized view logs on regular base tables. Assume that the following complex update scenario happens:

<tables id="TABLE-US-00006" num="00006"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="2"><colspec colname="1" colwidth="14pt" align="left" /><colspec colname="2" colwidth="203pt" align="left" /><thead><row><entry namest="1" nameend="2" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /><entry>Delete from FACT where month = 199802 and dollar_sales &lt; 10.</entry></row><row><entry /><entry>Insert into FACT with new rows of data.</entry></row><row><entry /><entry>Update PROMOTION set promotion_key=100 where &lt;condition&gt;</entry></row><row><entry /><entry>Insert into TIME with new rows.</entry></row><row><entry /><entry>Add and Drop partitions in FACT.</entry></row><row><entry /><entry>Split one partition in STORE into two and add more rows.</entry></row><row><entry namest="1" nameend="2" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

As seen in <figref idrefs="DRAWINGS">FIG. 1</figref>, phase <b>1</b> of the refresh execution deals with the changes in the regular base tables using materialized view logs. The non-PCT table changes are applied using the materialized view logs. The application of the non-PCT table changes can be written as ΔTIME×FACT×STORE×PROMOTION+TIME×FACT×STORE×ΔPROMOTION.

In this example, TIME and PROMOTION were changes with new rows are inserted and some rows are updated, respectively. Those changes are recorded in their corresponding materialized view logs. Then, the changes in TIME and PROMOTION are individually used to join with other base tables to figure out what changes should be made to SUMMARY_MAV.

The following refresh statements are generated to refresh the materialized view SUMMARY_MAV for phase <b>1</b> with the changes from TIME and PROMOTION. It is noted that there are two MERGE INTO statements to apply changes from TIME and PROMOTION individually. There is a DELETE statement to remove intermediate results whose count(*) value becomes 0 (rows with count(*)=0 should be removed from the materialized view as they are not semantically meaningful).

<tables id="TABLE-US-00007" num="00007"><table frame="none" colsep="0" rowsep="0" pgwide="1"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="273pt" align="left" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry>/* MV_REFRESH (MRG) */ MERGE INTO “GROCERY”.“SUMMARY_MAV” “SNA$”</entry></row><row><entry>USING</entry></row><row><entry>(SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */</entry></row><row><entry> “MAS$3”.“WEEK” “GB0”,</entry></row><row><entry> “MAS$2”.“STORE_KEY” “GB1”, “MAS$2”.“STORE_NAME” “GB2”,</entry></row><row><entry> “DLT$1”.“PROMOTION_KEY” “GB3”,</entry></row><row><entry> SUM(DECODE(“DLT$1”.“DML$$”, ‘I’, 1, −1)*</entry></row><row><entry>DECODE((“MAS$3”.“DOLLAR_SALES”), NULL, 0, 1)) “D0”,</entry></row><row><entry> SUM(DECODE(“DLT$1”.“DML$$”, ‘I’, 1, −1)) “D1”,</entry></row><row><entry> NVL(SUM(DECODE(“DLT$1”.“DML$$”, ‘I’, 1, −1)*</entry></row><row><entry>(“MAS$3”.“DOLLAR_SALES”)), 0) “G0”</entry></row><row><entry> FROM (SELECT “MAS$”.“ROWID” “RID$” , “MAS$”.“TIME_KEY”</entry></row><row><entry>  FROM “GROCERY”.“TIME” “MAS$”) AS OF SNAPSHOT (:3) “PRE$0” ,</entry></row><row><entry>  (SELECT /*+ CARDINALITY(MAS$ 2) */</entry></row><row><entry>   CHARTOROWID(“MAS$”.“M_ROW$$”) RID$,</entry></row><row><entry>   “MAS$”.“PROMOTION_KEY”,</entry></row><row><entry>   DECODE(“MAS$”.OLD_NEW$$, ‘N’, ‘I’, ‘D’) DML$$</entry></row><row><entry>  FROM “GROCERY”.“MLOG$_PROMOTION” “MAS$”</entry></row><row><entry>  WHERE “MAS$”.SNAPTIME$$ &gt; :1 ) AS OF SNAPSHOT (:3) “DLT$1” ,</entry></row><row><entry>  (SELECT “MAS$”.“ROWID” “RID$”,</entry></row><row><entry>   “MAS$”.“STORE_NAME”, “MAS$”.“STORE_KEY”</entry></row><row><entry>  FROM “GROCERY”.“STORE” “MAS$”) AS OF SNAPSHOT (:3) “MAS$2” ,</entry></row><row><entry>  (SELECT “MAS$”.“ROWID” “ RID$”,</entry></row><row><entry>   “MAS$”.“WEEK”, “MAS$”.“STORE_KEY”, “MAS$”.“TIME_KEY”,</entry></row><row><entry>   “MAS$”.“PROMOTION_KEY”, “MAS$”.“DOLLAR_SALES”</entry></row><row><entry>  FROM “GROCERY”.“FACT” “MAS$”) AS OF SNAPSHOT (:3) “MAS$3”</entry></row><row><entry>WHERE (“MAS$2”.“STORE_KEY”=“MAS$3”.“STORE_KEY” AND</entry></row><row><entry> “MAS$3”.“TIME_KEY”=“PRE$0”.“TIME_KEY” AND</entry></row><row><entry> “MAS$3”.“PROMOTION_KEY”=“DLT$1”.“PROMOTION_KEY”) AND</entry></row><row><entry> SYS_OP_MAP_NONNULL(“PRE$0”.“RID$”) NOT IN</entry></row><row><entry>  (SELECT * FROM</entry></row><row><entry>  (SELECT /*+ CARDINALITY(MAS$ 1) ANTIJOIN */</entry></row><row><entry>    SYS_OP_MAP_NONNULL(CHARTOROWID(“MAS$”.“M_ROW$$”)) RID$</entry></row><row><entry>   FROM “GROCERY”.“MLOG$_TIME” “MAS$”</entry></row><row><entry>   WHERE “MAS$”.SNAPTIME$$ &gt; :2 ) AS OF SNAPSHOT (:3) MAS$)</entry></row><row><entry> GROUP BY “MAS$3”.“WEEK”,“MAS$2”.“STORE_KEY”,</entry></row><row><entry>    “MAS$2”.“STORE_NAME”,“DLT$1”.“PROMOTION_KEY”)“AV$”</entry></row><row><entry>ON (SYS_OP_MAP_NONNULL(“SNA$”.“WEEK”)=</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“AV$”.“GB0”) AND</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“SNA$”.“STORE_KEY”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB1”) AND</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“SNA$”.“STORE_NAME”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB2”) AND</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“SNA$”.“PROMOTION_KEY”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB3”))</entry></row><row><entry>WHEN MATCHED THEN</entry></row><row><entry> UPDATE SET “SNA$”.“CNT_DSALES”=“SNA$”.“CNT_DSALES”+“AV$”.“D0”,</entry></row><row><entry> “SNA$”.“CNT”=“SNA$”.“CNT”+“AV$”.“D1”,</entry></row><row><entry> “SNA$”.“AVG_SALES”=</entry></row><row><entry>  DECODE(“SNA$”.“CNT_DSALES”+“AV$”.“D0”,0,TO_NUMBER(NULL),</entry></row><row><entry>  ((NVL(“SNA$”.“AVG_SALES”,0)*“SNA$”.“CNT_DSALES”)+“AV$”.“G0”)/</entry></row><row><entry>  (“SNA$”.“CNT_DSALES”+“AV$”.“D0”))</entry></row><row><entry>WHEN NOT MATCHED THEN</entry></row><row><entry> INSERT (“SNA$”.“WEEK”, “SNA$”.“STORE_KEY”, “SNA$”.“STORE_NAME”,</entry></row><row><entry>   “SNA$”.“PROMOTION_KEY”,</entry></row><row><entry>   “SNA$”.“CNT_DSALES”, “SNA$”.“CNT”, “SNA$”.“AVG_SALES”)</entry></row><row><entry> VALUES (“AV$”.“GB0”, “AV$”.“GB1”, “AV$”.“GB2”, “AV$”.“GB3”,</entry></row><row><entry>   “AV$”.“D0”, “AV$”.“D1”,</entry></row><row><entry>DECODE(“AV$”.“D0”,0,TO_NUMBER(NULL),“AV$”.“G0”/“AV$”.“D0”));</entry></row><row><entry>/* MV_REFRESH (DEL) */ DELETE FROM “GROCERY”.“SUMMARY_MAV” “SNA$”</entry></row><row><entry>WHERE “SNA$”.“CNT”=0;</entry></row><row><entry>/* MV_REFRESH (MRG) */ MERGE INTO “GROCERY”.“SUMMARY_MAV” “SNA$”</entry></row><row><entry>USING</entry></row><row><entry> (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */</entry></row><row><entry> “MAS$3”.“WEEK” “GB0”, “MAS$2”.“STORE_KEY” “GB1”,</entry></row><row><entry> “MAS$2”.“STORE_NAME” “GB2”, “MAS$1”.“PROMOTION_KEY” “GB3”,</entry></row><row><entry> SUM(1* DECODE((“MAS$3”.“DOLLAR_SALES”),NULL,0,1)) “D0”, SUM(1)</entry></row><row><entry>“D1”,</entry></row><row><entry> NVL(SUM(1* (“MAS$3”.“DOLLAR_SALES”)), 0) “G0”</entry></row><row><entry> FROM (SELECT /*+ CARDINALITY(MAS$ 1) */</entry></row><row><entry>   CHARTOROWID(“MAS$”.“M_ROW$$”) RID$,</entry></row><row><entry>   “MAS$”.“TIME_KEY”</entry></row><row><entry>  FROM “GROCERY”.“MLOG$_TIME” “MAS$”</entry></row><row><entry>  WHERE “MAS$”.SNAPTIME$$ &gt; :1 ) AS OF SNAPSHOT (:2) “DLT$0” ,</entry></row><row><entry>  (SELECT “MAS$”.“ROWID” “RID$” , “MAS$”.“PROMOTION_KEY”</entry></row><row><entry>  FROM “GROCERY”.“PROMOTION” “MAS$”) AS OF SNAPSHOT (:2)</entry></row><row><entry>“MAS$1” ,</entry></row><row><entry>  (SELECT “MAS$”.“ROWID” “RID$” , “MAS$”.“STORE_NAME”,</entry></row><row><entry>   “MAS$”.“STORE_KEY”</entry></row><row><entry>  FROM “GROCERY”.“STORE” “MAS$”) AS OF SNAPSHOT (:2) “MAS$2”,</entry></row><row><entry>  (SELECT “MAS$”.“ROWID” “RID$”, “MAS$”.“WEEK”,</entry></row><row><entry>   “MAS$”.“STORE_KEY”, “MAS$”.“TIME_KEY”,</entry></row><row><entry>   “MAS$”.“PROMOTION_KEY”, “MAS$”.“DOLLAR_SALES”</entry></row><row><entry>  FROM “GROCERY”.“FACT” “MAS$”) AS OF SNAPSHOT (:2) “MAS$3”</entry></row><row><entry>WHERE (“MAS$2”.“STORE_KEY”=“MAS$3”.“STORE_KEY” AND</entry></row><row><entry>   “MAS$3”.“TIME_KEY”=“DLT$0”.“TIME_KEY” AND</entry></row><row><entry>   “MAS$3”.“PROMOTION_KEY”=“MAS$1”.“PROMOTION_KEY”)</entry></row><row><entry> GROUP BY “MAS$3”.“WEEK”,“MAS$2”.“STORE_KEY”,“MAS$2”.“STORE_NAME”,</entry></row><row><entry>    “MAS$1”.“PROMOTION_KEY”)“AV$”</entry></row><row><entry>ON (SYS_OP_MAP_NONNULL(“SNA$”.“WEEK”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB0”) AND</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“SNA$”.“STORE_KEY”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB1”) AND</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“SNA$”.“STORE_NAME”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB2”) AND</entry></row><row><entry>  SYS_OP_MAP_NONNULL(“SNA$”.“PROMOTION_KEY”)=</entry></row><row><entry>   SYS_OP_MAP_NONNULL(“AV$”.“GB3”))</entry></row><row><entry>WHEN MATCHED THEN</entry></row><row><entry> UPDATE SET “SNA$”.“CNT_DSALES”=“SNA$”.“CNT_DSALES”+“AV$”.“D0”,</entry></row><row><entry>  “SNA$”.“CNT”=“SNA$”.“CNT”+“AV$”.“D1”,</entry></row><row><entry>  “SNA$”.“AVG_SALES”=</entry></row><row><entry>   DECODE(“SNA$”.“CNT_DSALES”+“AV$”.“D0”,0,TO_NUMBER(NULL),</entry></row><row><entry>((NVL(“SNA$”.“AVG_SALES”,0)*“SNA$”.“CNT_DSALES”)+“AV$”.“G0”)/</entry></row><row><entry>    (“SNA$”.“CNT_DSALES”+“AV$”.“D0”))</entry></row><row><entry>WHEN NOT MATCHED THEN</entry></row><row><entry> INSERT (“SNA$”.“WEEK”, “SNA$”.“STORE_KEY”, “SNA$”.“STORE_NAME”,</entry></row><row><entry>   “SNA$”.“PROMOTION_KEY”,</entry></row><row><entry>   “SNA$”.“CNT_DSALES”, “SNA$”.“CNT”, “SNA$”.“AVG_SALES”)</entry></row><row><entry> VALUES ( “AV$”.“GB0”, “AV$”.“GB1”, “AV$”.“GB2”, “AV$”.“GB3”,</entry></row><row><entry>   “AV$”.“D0”, “AV$”.“D1”,</entry></row><row><entry>   DECODE(“AV$”.“D0”, 0, TO_NUMBER(NULL),</entry></row><row><entry>   AV$”.“G0”/“AV$”.“D0”))</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

The refresh statement generation for Phase <b>1</b> of <figref idrefs="DRAWINGS">FIG. 1</figref> is the same as that for log-based incremental refresh. The difference is that only the changes on the non-PCT tables are addressed in the above phase and the changes (often large but focused in a small number of partitions) on PCT tables are intentionally left to the next phase.

Referring to <figref idrefs="DRAWINGS">FIG. 2</figref>, phase <b>2</b> of the mix incremental refresh execution is to specifically apply PCT base table changes to the materialized view. <figref idrefs="DRAWINGS">FIG. 2</figref> illustrates the application of PCT table changes using partition change tracking information. The data in the materialized view affected by partition changes in FACT <b>101</b> and STORE <b>102</b> are recomputed. In this example, FACT and STORE are PCT base tables and their changes are tracked by partition scn (system change number) changes on all their partitions. In this phase, there are two basic operations: one is removing affected rows in the materialized view and the other is re-computing new rows to insert into the materialized view.

<figref idrefs="DRAWINGS">FIG. 3</figref> illustrates a basic flow diagram incorporating features of the present invention. The materialized view <b>302</b> can be generated based on the database <b>301</b>. DML/Direct load changes <b>310</b> made to the database <b>301</b> can be received in the table MV Log and/or direct load log <b>312</b>. Partition maintenance operation changes <b>320</b> to the data base <b>301</b> are received <b>322</b> in the partition maintenance log. During the first phase shown in <figref idrefs="DRAWINGS">FIG. 1</figref>, log based incremental refresh changes <b>303</b> are made using the materialized view log and/or direct load log <b>312</b>. Then, in the second phase, the partition change tracking refresh <b>304</b>, based on the partition maintenance operation log with SCN <b>322</b>, is performed to form the updated materialized view <b>305</b>.

The following refresh statements are generated to refresh the materialized view SUMMARY_MAV in phase <b>2</b> with the changes from FACT and STORE. There are two statements generated: one is DELETE statement and the other is INSERT statement. The DELETE statement removes the affected rows in the materialized view based on the partition bound information (i.e., list of WEEK values in FACT and range predicate of STORE_KEY in STORE). Then, an INSERT statement using the same partition bound information is used to re-compute new rows to be inserted into the materialized view.

<tables id="TABLE-US-00008" num="00008"><table frame="none" colsep="0" rowsep="0" pgwide="1"><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="273pt" align="left" /><thead><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry>/* MV_REFRESH (DEL) */ DELETE FROM “GROCERY”.“PART_EUT_DM_MV”</entry></row><row><entry>WHERE (16 &lt;= “STORE_KEY” AND “STORE_KEY” &lt; 25) OR</entry></row><row><entry>  (“WEEK” IN (199748, 199749, 199750)) OR . . .</entry></row><row><entry>  );</entry></row><row><entry>/* MV_REFRESH (INS) */ INSERT /*+ BYPASS_RECURSIVE_CHECK */</entry></row><row><entry>INTO “GROCERY”.“PART_EUT_DM_MV”</entry></row><row><entry>SELECT /*+ X_DYN_PRUNE */ “PF”.“WEEK” , “S”.“STORE_KEY” ,</entry></row><row><entry> “S”.“STORE_NAME” ,“P”.“PROMOTION_KEY” , AVG(“PF”.“DOLLAR_SALES”),</entry></row><row><entry> COUNT(*) , COUNT(“PF”.“DOLLAR_SALES”)</entry></row><row><entry>FROM “TIME1” “T”,“PROMOTION1” “P”,“PART_STORE” “S”,“PART_FACT_TIME”</entry></row><row><entry>“PF”</entry></row><row><entry>WHERE (“S”.“STORE_KEY”=“PF”.“STORE_KEY” AND</entry></row><row><entry>  “PF”.“TIME_KEY”=“T”.“TIME_KEY” AND</entry></row><row><entry>  “PF”.“PROMOTION_KEY”=“P”.“PROMOTION_KEY”) AND</entry></row><row><entry>  ( (16 &lt;= “S”.“STORE_KEY” AND “S”.“STORE_KEY” &lt; 25) OR</entry></row><row><entry>   ( (“PF”.“WEEK” IN (199748, 199749, 199750)) OR . . .</entry></row><row><entry>    ))</entry></row><row><entry>GROUP BY</entry></row><row><entry>“PF”.“WEEK”,“S”.“STORE_KEY”,“S”.“STORE_NAME”,“P”.“PROMOTION_KEY”;</entry></row><row><entry namest="1" nameend="1" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

Furthermore, if the materialized view is partitioned on the same column as one of the PCT base tables, the DELETE statement in phase <b>2</b> can further be optimized using TRUNCATE PARTITION statements which can efficiently remove affected rows in the materialized view.

This solution combines and takes advantage of both log-based (FAST) refresh and PCT incremental refresh to achieve the functionality of handling arbitrarily complex change scenarios and lifting big restrictions on materialized view log dependency. Also, it delivers better performance for materialized view incremental refresh on common change scenarios in the data warehouse applications.

One mechanism of partition change tracking and logical partitioning of a materialized view can include logically dividing the materialized view into multiple sections so that each of the sections has its own freshness state and enable query rewrite even when the materialized view is not globally fresh. This is achieved by logical dividing. The data in the fresh sections can be used for query rewrite to speed up the query processing. A partition-change-based incremental refresh approach is provided for the materialized view. Incremental refresh therefore becomes possible after the partition maintenance operations happen to the base partitioned table or after some cases that can not be handled by the existing row-change-based refresh approach. This can be achieved by utilizing the partitioned base table of the materialized view. When one or more base tables are partitioned (i.e., by range or by list of values) and the defining query of the materialized view has the partition key/marker column in its SELECT list, then the partition change tracking mechanism becomes possible.

<figref idrefs="DRAWINGS">FIG. 4</figref> illustrates a computer system <b>400</b> upon which an embodiment of the invention may be implemented. As shown in <figref idrefs="DRAWINGS">FIG. 4</figref>, the system <b>400</b> generally includes a bus <b>402</b> or other communication mechanism for communicating information, and a processor <b>404</b> coupled with bus <b>402</b> for processing information. Computer system <b>400</b> also includes a main memory <b>406</b>, such as a random access memory (RAM) or other dynamic storage device, coupled to bus <b>402</b> for storing information and instructions to be executed by processor <b>404</b>. Main memory <b>406</b> also may be used for storing temporary variables or other intermediate information during execution of instructions to be executed by processor <b>404</b>. System <b>400</b> can also include a read only memory (ROM) <b>408</b> or other static storage device coupled to bus <b>402</b> for storing static information and instructions for processor <b>404</b>. A storage device <b>410</b>, such as a magnetic disk or optical disk, can be coupled to bus <b>402</b> for storing information and instructions.

Computer system <b>400</b> may also be coupled via bus <b>402</b> to a display <b>412</b>, such as a cathode ray tube (CRT), for displaying information to a computer user. An input device <b>414</b>, including alphanumeric and other keys, is coupled to bus <b>402</b> for communicating information and command selections to processor <b>404</b>. Another type of user input device is cursor control <b>416</b>, such as a mouse, a trackball, or cursor direction keys for communicating direction information and command selections to processor <b>404</b> and for controlling cursor movement on display <b>412</b>. 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 disclosed embodiments are related to the use of computer system <b>400</b> for the mix incremental refresh of materialized views. According to one embodiment of the invention, the mix incremental refresh process of the disclosed embodiments can be provided by computer system <b>400</b> in response to processor <b>404</b> executing one or more sequences of one or more instructions contained in main memory <b>406</b>. Such instructions may be read into main memory <b>406</b> from another computer-readable medium, such as storage device <b>410</b>. Execution of the sequences of instructions contained in main memory <b>406</b> causes processor <b>404</b> to perform the process steps described herein. One or more processors in a multi-processing arrangement may also be employed to execute the sequences of instructions contained in main memory <b>406</b>. In alternative embodiments, hardwired 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 <b>404</b> 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 <b>410</b>. Volatile media can include for example, dynamic memory, such as main memory <b>406</b>. Transmission media can include for example, coaxial cables, copper wire and fiber optics, including the wires that comprise bus <b>402</b>. Transmission media can also take the form of acoustic or light waves, such as those generated during radio wave and infrared 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, a RAM, a PROM, an EPROM, a FLASH-EPROM, any other memory chip or cartridge, 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 <b>404</b> 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 <b>400</b> can receive the data on the telephone line and use an infrared transmitter to convert the data to an infrared signal. An infrared detector coupled to bus <b>402</b> can receive the data carried in the infrared signal and place the data on bus <b>402</b>. Bus <b>402</b> carries the data to main memory <b>406</b>, from which processor <b>404</b> retrieves and executes the instructions. The instructions received by main memory <b>406</b> may optionally be stored on storage device <b>410</b> either before or after execution by processor <b>404</b>.

As illustrated in <figref idrefs="DRAWINGS">FIG. 4</figref>, the computer system <b>400</b> also includes a communication interface <b>418</b> coupled to bus <b>402</b>. Communication interface <b>418</b> provides a two-way data communication coupling to a network link <b>420</b> that is connected to a local network <b>422</b>. For example, communication interface <b>418</b> 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 <b>418</b> 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 <b>418</b> sends and receives electrical, electromagnetic or optical signals that carry digital data streams representing various types of information.

Network link <b>420</b> typically provides data communication through one or more networks to other data devices. For example, network link <b>420</b> may provide a connection through local network <b>422</b> to a host computer <b>424</b> or to data equipment operated by an Internet Service Provider (ISP) <b>426</b>. ISP <b>426</b> in turn provides data communication services through the worldwide packet data communication network now commonly referred to as the “Internet” <b>428</b>. Local network <b>422</b> and Internet <b>428</b> both use electrical, electromagnetic or optical signals that carry digital data streams. The signals through the various networks and the signals on network link <b>420</b> and through communication interface <b>418</b>, which carry the digital data to and from computer system <b>400</b>, are exemplary forms of carrier waves transporting the information.

Computer system <b>400</b> can send messages and receive data, including program code, through the network(s), network link <b>420</b> and communication interface <b>418</b>. In the Internet example, a server <b>430</b> might transmit a requested code for an application program through Internet <b>428</b>, ISP <b>426</b>, local network <b>422</b> and communication interface <b>418</b>. In accordance with the invention, one such downloaded application provides for incrementally refreshing materialized views as described herein.

The received code may be executed by processor <b>404</b> as it is received, and/or stored in storage device <b>410</b>, or other non-volatile storage for later execution. In this manner, computer system <b>400</b> may obtain application code in the form of a carrier wave.

The mix incremental refresh mechanism of the present invention delivers both functionality and performance advantages in the area of materialized view incremental maintenance. Some of these benefits include enabling incremental refresh of the materialized view in the general update scenario where both partitioned and non-partitioned base tables are changed with mixed operations including conventional DMLs, direct patch load and partition maintenance operations. Previously, no existing incremental refresh method could handle such general cases. In the data warehouse environment where the data stored in tables are often very large, table partitioning has been an efficient and widely adopted approach to manage data. In this environment, not only base tables but also materialized views are likely partitioned. Therefore, such update scenarios with mixed change operations are becoming more and more common. Supporting incremental refresh for such cases is increasingly important. Without it, only expensive complete refresh method can be used to synchronize the data from the base tables to the materialized view.

The present invention also eliminates the dependency on the materialized view for partitioned base tables, which is a requirement for conventional incremental refresh method. Without the dependency, the partitioned base tables will no longer need materialized view logs to support materialized view incremental refresh under all kinds of update scenarios. The most significant value is to save the heavy cost of materialized view log both in storage and in maintenance. With the present invention there is no need to create a materialized view log for the partitioned base table. Thus, more storage space will become available, and since there is no materialized view log, there is no need to record the changes of the base table while doing conventional DML operations. As a result, conventional DML operations can perform cleaner and faster without such logging burden. Finally, in the materialized view refresh process of the present invention, there is no need to do refresh log setup and purging. Currently, such log maintenance is a part of the refresh process and causes big performance overhead during refresh.

It should be understood that the foregoing description is only illustrative of the invention. Various alternatives and modifications can be devised by those skilled in the art without departing from the invention. Accordingly, the present invention is intended to embrace all such alternatives, modifications and variances which fall within the scope of the appended claims.