A precomputed result set (PRS) is a view for which the result is computed, stored, and available for future use. Once configured for precomputed result sets, Adaptive Server 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 Adaptive Server 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.
When the optimizer rewrites a query using a precomputed result set, it decides which precomputed result set is the best candidate. If the optimizer chooses to replace all, or part, of a query with a precomputed result set, it also adds any necessary compensation to the rewritten query (that is, any predicates needed to ensure the rewritten query is equivalent to the original user query). For example, if the user query includes a join of:
c1=c2 and c2=c3 and c3=c4
but the precomputed result set includes a join for:
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 selects.
