5.9.2  Incrementally Updating a Materialized View

You can schedule a materialized view to be regenerated on a predefined schedule by assigning a “cycle” to the view. (See Creating a Materialized View for more information.) This allows the materialized view to be updated with new data from the database. However, for a large data set, repeatedly regenerating the materialized view in its entirety may prove inefficient.

To improve performance, you can update the materialized view incrementally based on a set of specified conditions. For example, you can selectively update the materialized view with data which has posted subsequent to the previous materialization. Likewise, you can delete selected records from the materialized view if they meet a specified condition, such as an expiration date.

You can specify an incremental update condition both for mergeable queries (e.g., JDBC queries) and for non-mergeable queries (e.g., non-JDBC queries, un-parsable queries, etc.), as described below.

Incrementally Updating a Mergeable Query

To specify the criteria that govern the incremental materialized view update for a mergeable query (i.e., standard, parsable, JDBC query), add the desired conditions to the MV Update tab of the Data Block in the Data Worksheet. The Data Block's MV Update condition will be tested each time an existing materialized view is regenerated (based on the cycle specified for the view), and the view regeneration will be limited to the additions and deletions specified by the condition. (See Defining a Materialized View Update Condition in Data Mashup for more information on how to define the update conditions in the MV Update tab.)

Incrementally Updating a Non-Mergeable Query

To specify the criteria that govern the incremental materialized view update for a non-mergeable query (e.g., non-JDBC query or un-parsable query), follow the steps below:

1. Using the Query Wizard in Style Studio, add the desired update condition to the query definition itself. For example:

SELECT ... FROM T1 WHERE (...) AND DATE_COL = $(MV.LastUpdateTime)

The update condition can use the special variables MV.LastUpda­teTime, MV.{Column Name}.Min, and MV.{Column Name}.Max, where “{Column Name}” is the materialized view column name with non-alphanumeric characters replaced by underscores. For example, a materialized view column named 'Year(Order Date)' should use parameter names MV.Year_Order_Date_.Min and MV.Year_Order_Date_.Max. (See Special Parameters for Material­ized View Update Conditions in Data Mashup for more informa­tion about these parameters.)

2. Press the 'Parameter' button on the Style Studio toolbar to open the 'Query Variable Definition' dialog box for the query. Disable parameter prompting and assign default values to the update condi­tion parameters so that the result set is not restricted.

 

This prevents the user from being prompted for these variables, and ensures that the data is not restricted during the initial materialized view generation. (See Query Parameters in Data Modeling for more information about the 'Query Variable Definition' dialog box.)

3. In the MV Update tab of the Worksheet Data Block, enable the 'Always append updates' option. (See Defining a Materialized View Update Condition in Data Mashup for more information about the MV Update tab.)

 

This ensures that the results of the non-mergeable query will be appended to the existing materialized view.

<< 5.9.1 Creating a Materialized View © 1996-2013 InetSoft Technology Corporation (v11.5) 5.9.3 Data Security with Materialized Views >>