Rewriting Queries

Query rewrite mechanisms generate alternative plans based on available precomputed results.

The alternative plans compete with other plans in the optimizer, and SAP ASE 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.

SAP ASE 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 SAP ASE 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