Query rewrite mechanisms generate alternative plans based on available precomputed results. The alternative plans compete with other plans in the optimizer, and Adaptive Server selects the one with the lowest estimated cost. However, the query rewrite mechanism works only with select queries; it does not consider insert, update, delete, and select into queries for rewrite.
Adaptive Server may rewrite an entire query to create an equivalent precomputed result set, or it may rewrite part of a query, depending on the query properties and the available precomputed result sets. The precomputed result set must completely cover the logical data set for queries that Adaptive Server rewrites.
For example, if you have a query similar to this, which is complicated and involves multitable joins and many predicates, groupings, and aggregations:
select t1.col1,t2.col1,t3.col1, sum(t1.col3),sum(t2.col3), sum(t3.col3) from t1, t2, t3 where t1.col1 = t2.col1 and t2.col1 = t3.col1 and t1.col2 < 60 and t1.col1 > 5 and t1.col2 + t2.col2 < 40 group by t1.col1, t2.col1, t3.col1
And create this precomputed result set:
create precomputed result set newprs as select t1.col1 as p11, t1.col2 as p12, t2.col1 as p21, t2.col2 as p22, t3.col1 as p31, t3.col2 as p32, sum(t1.col3) as agg_s13,sum(t2.col3) as agg_s23, sum(t3.col3) as agg_s33 from t1, t2, t3 where t1.col1 = t2.col1 and t1.col2 < 60 and t1.col2 + t2.col2 < 40 group by t1.col1, t2.col1, t3.col1, t1.col2, t2.col2, t3.col2
The query rewrite mechanism may alter the original query to something similar to this, which is much simpler and may be cheaper to execute:
select p11,p21,p31, sum(agg_s13),sum(agg_s23),sum(agg_s33) from newprs where p21 = p31 and p11 > 5 group by p11, p21, p31
