Roy - query rewrite works for a number of different situations:
- Exact match
- Aggregate rollup - the query requires an aggregate at a level higher
than is available in a summary - for instance, the query requires a
yearly aggregate, but the summary only contains monthly values. Query
rewrite will use 12 rows from the summary table to produce the yearly
total. You will need to define and validate a dimension hierarchy to
have this type of rewrite happen.
- Aggregate to all - the query requires an aggregate on only one of the
dimension columns in the summary - for instance, if I have a summary of
sales by region, product and month, and the query wants a monthly total.
Query re-write will aggregate the product and region sub totals to
derive the overall monthly total.
- Summary Joinback - the query requires some data not stored in the
summary, but can use the summary information to facilitate a join back
into the base tables - basically, the summary drives the join. This last
case is a special case, and may also require the definition of dimension
hierarchies.
I can send a worked example of each if you like.
rssacks_at_ibm.net wrote:
>
> Our initial tests have shown that the query rewrite function just works when
> there is an exact match to the query in the materialized view. Does anyone
> have a different experience.
>
> Roy
--
Regards,
Mark Townsend
Senior Product Manager Ph: (650) 633 5764
Server Division Fx: (650) 506 7222
Oracle Corporation Email: mtownsen_at_us.oracle.com
Received on Fri Jun 25 1999 - 02:21:30 CDT
