Skip to Content
Every one knows that we can use aggregate aware feature of Business Objects in universe in order to improve query performance by using summary tables or MV’s.

Similar results can be achieved using database also. Means if you are using Oracle database Oracle can dynamically use your summary views, instead of hitting tables.

Lets take a look at it.

Suppose you have summarized materialized view as

CREATE MATERIALIZED VIEW DaySummary_mv
  AS
  SELECT d.day,AVG(s.amount_sold)
   FROM sales s, daytab d
   WHERE s.date = d.date
   GROUP BY d.day

CREATE MATERIALIZED VIEW MonthSummary_mv
  AS
  SELECT d.day,AVG(s.amount_sold)
   FROM sales s, daytab d
   WHERE s.month = d.month
   GROUP BY d.month

Now if you have created a report in web intelligence which generates query as

SELECT d.day,AVG(s.amount_sold)
   FROM sales s, daytab d
   WHERE s.month = d.month
   GROUP BY d.month

Then Oracle Database will automatically change the query to

select * from MonthSummary_mv

in order to use the summary materialized view instead of hitting tables. which would certainly improve your query performace.

To enable this feature, You would need to enable Oracle QUERY REWRITE feature.

To achive this either you would need to modify view definition as

CREATE MATERIALIZED VIEW MonthSummary_mv
ENABLE QUERY REWRITE
  AS
  SELECT d.day,AVG(s.amount_sold)
   FROM sales s, daytab d
   WHERE s.month = d.month
   GROUP BY d.month

or if view is already created use alter:

alter materialized view mv_name ENABLE QUERY REWRITE ;

Please read more about this feature in Oracle documentation in order to use this feature. It might be helpful in some situation to improve the report performance.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply