Craig Ringer, 22.09.2011 08:34:
> - You don't see materialized views without selective updating ("fast
> refresh") as useful. [I disagree, though I can see how it wouldn't be
> very useful for the particular use case you're facing.]
One thing that is often overlooked and that I find most useful is the rewrite magic that Oracle can use with MVIEWS.
Assume an application is running the following statement:
select department_id,
count(*) as num_employees
from employees
group by department_id;
and due to the size of the table this statement is slow. One can create a materialized view like this:
create materialized view num_emps
refresh complete on commit
enable query rewrite
as
select department_id,
count(*) as num_employees
from employees
group by department_id;
Now each time the application runs the original statement, will silently rewrite the query into "SELECT * FROM
num_emps"which is a lot faster than the real statement.
Oracle will know whether the view is stale and will do the rewriting only if this is applicable.
Regards
Thomas