Query Rewrite feature in Oracle

Hi Guys,

Query rewrite is a powerful feature in Oracle which you can use to speed up your cognos queries .In short you can have a materialised view which contains summary information and you dont need to include this materialized view in Framework.

When cognos run the query in database.Oracle will replace the query with MV results if it finds them suitable .

Cognos Does not support Aggregate awareness and this has to be implemented from database. For details you can read my article on Aggregate Awareness Article  

Taken from Oracle site 

http://docs.oracle.com/cd/B28359_01/server.111/b28313/qrbasic.htm

When base tables contain large amount of data, it is expensive and time-consuming to compute the required aggregates or to compute joins between these tables. In such cases, queries can take minutes or even hours. Because materialized views contain already precomputed aggregates and joins, Oracle Database employs an extremely powerful process called query rewrite to quickly answer the query using materialized views.

One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.

A query undergoes several checks to determine whether it is a candidate for query rewrite. If the query fails any of the checks, then the query is applied to the detail tables rather than the materialized view. This can be costly in terms of response time and processing power.

The optimizer uses two different methods to recognize when to rewrite a query in terms of a materialized view. The first method is based on matching the SQL text of the query with the SQL text of the materialized view definition. If the first method fails, the optimizer uses the more general method in which it compares joins, selections, data columns, grouping columns, and aggregate functions between the query and materialized views.