Re: Materialized views in Oracle

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