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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to