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.
