Precomputed Result Sets

A precomputed result set is a view for which the result is computed, stored, and available for future use. Once configured for precomputed result sets, SAP ASE precomputes queries and attempts to use the precomputed result during subsequent iterations. Precomputed result sets are also called materialized views.

Conceptually, a precomputed result set is both a view (because it includes query definition stored in the system tables) and a table (because it includes persistent data). You can perform many of the same operations that you perform on tables on precomputed result sets, including creating indexes and running update statistics.

Once SAP ASE is configured to use precomputed result sets, the optimizer attempts to automatically rewrite each query using a precomputed result set. However, the final plan the optimizer selects is primarily cost based.

 c1=c2 and c2=c3 and c3=c4

c1=c2 and c3=c4

the rewritten query using the precomputed result set must have a compensation predicate similar to c1=c3 to form an equivalent query.

Like an index, a precomputed result set has a maintenance cost for concurrent insert, update, and delete statements. Generally, precomputed result-set maintenance overhead consists of more than maintaining the indexes when the definition involves multiple table joins. Consequently, precomputed result sets are unsuitable for OLTP with heavy concurrent insert, update, and delete statements and simple index-based select statements.

• Benefits of Precomputed Result Sets
  Whether your site benefits from precomputed result sets depends on how they are designed.
• Configuring SAP ASE for Precomputed Result Sets
  Before you create or alter precomputed result sets, verify that a number of session set parameters are set correctly
• Creating Precomputed Result Sets
  To use precomputed result sets for your queries, issue the set materialized_view_optimization command for the session.
• Identifying Precomputed Result Sets
  sp_help includes information about precomputed result sets in the Object_type and object_status columns.
• 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.
• Altering Precomputed Result Sets
  Use the alter command to change the precomputed result set’s policies or properties.
• Dropping or Truncating Precomputed Result Sets
  Dropping a precomputed result set deletes its data, removes any system table entries, and deletes the precomputed result set.
• Configuring Staleness
  Precomputed result sets rely on updates from their base tables to ensure data is current.
• Querying Precomputed Result Sets
  SAP ASE allows you to select information from precomputed result sets, but you cannot insert, update, or delete information from the precomputed result set. Instead, you must insert, update, or delete information from the base tables, and then refresh the precomputed result set.
• Rewriting Queries
  Query rewrite mechanisms generate alternative plans based on available precomputed results.
• Replicating Precomputed Result Sets
  SAP ASE supports replication of some precomputed result set commands.
• Restrictions for Precomputed Result Sets
  Precomputed result sets include restrictions.