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:
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
  1. 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
  2. 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 
  3. 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
  4. 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
    
     
  5. 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.