Refreshing Precomputed Result Sets

Precomputed result sets do not necessarily remain synchronized with the base tables from which they are constructed, and must also be refreshed, either automatically or manually.

Configure the refresh policy when you create the precomputed result set, or later with the alter precomputed result set command:

Immediate refresh – the precomputed result set is updated during the same transaction that updates the base tables. This is the default option. However, creating a precomputed result set for immediate refresh requires the user to own all the tables in the definition query. You can grant and revoke access permissions for precomputed result sets created with the immediate refresh parameter for the select, update statistics, and delete statistics commands.
Manual refresh – the precomputed result set is updated with an explicit refresh command. Because manual refreshes are not maintained, SAP ASE considers the data they contain to be stale (even immediately after you issue refresh), and selects these precomputed result sets for query rewrites only if the query is acceptable with stale data. The refresh command is executed under isolation level 1 or above.

The syntax to manually refresh a precomputed result set is:

refresh {precomputed result set | materialized view} 
	[owner_name.]prs_name

If the schema of any of the base tables from which the precomputed result set is derived has changed, or if it was dropped and re-created (that is, the object ID has changed), the refresh command fails and returns an error indicating the precomputed result set must be dropped and re-created.

Only the owner of the precomputed result set can use the refresh command. If a user has permission to update the base table, he or she can also maintain the precomputed result set.

In most situations, the optimizer should use precomputed result sets with immediate refresh instead of manual refresh for query rewriting (unless you set materialized_view_optimization to stale).

Manually refreshing the precomputed result set is best when you control when insert, update, and delete statements occur. After they occur, perform a planned manual refresh of the precomputed result sets, then use the precomputed result sets to help your read-only applications. However, be aware of the time and extra disk space required to perform a manual refresh and plan accordingly.

Note: After creating a precomputed result set, its owner may not have select permission on the base tables. If this occurs, manually refreshing the precomputed result set maintenance may fail, and it is not updated with the new changes from the base table. You cannot execute the refresh command as part of a batch.

This example illustrates how to refresh a precomputed result set

Create table t1:

create table t1 (
c1 int,
c2 int,
c3 char(5))

And populate it with this data:

c1          c2          c3
----------- ----------- -----
          1           3  Aagg
          2           8  Xyz

Create table t2:

create table t2
(a1 int,
a2 int,
a3 char(5))

And populate it with this data:

a1          a2          a3 
----------- ----------- -----
          1           5  Ghr
          2           1  Gser
          3           6  agfh

Create the prs_1 precomputed result set:

create precomputed result set prs_1
unique (t1.c1, t2.a2)
as select t1.c1, t2.a2 from t1, t2 where t1.c1=t2.a1

prs_1 is created and populated with these initial rows:

c1          a2
----------- -----------
          1           5
          2           1

If you insert the values 3, 7, and “fhi” into t1, prs_1 is immediately updated with the values 3 and 6:

c1          a2
----------- -----------
          1           5
          2           1
          3           6

If you delete rows from t2 where a1 = 2, prs_1 is immediately updated with this change:

c1          a2
----------- -----------
          1           5
          3           6

If SAP ASE rolls back the transaction updating the base table, it also rolls back the immediate update on the base table’s precomputed result set as part of the same transaction.