Posted by Alexander Kuznetsov on 02/02/06 17:24

> SQL Server has Indexed Views which I believe are better than the Oracle
> materialised because once set up they are dynamically maintained so are
> always up-to-date

Tony,
I don't think so. Oracle can refresh an MV on commit if you wish so,
which gives you an always up-to-date MV.

http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch5.htm

Refresh on commit, as opposed to refresh as part of every DML
statement, as SQL Server does,
is also in some cases better, as locks on MV are held for shorter time.

Also Oracle can store only the relevant changes and let you
incrementally (=quickly) refresh the MV later on.
This is convenient in OLTP situations when it's better to display
slightly stale data
than to deal with lock contention on MVs. Besides, Oracle have some
convenient features such as check constraints on MVs.

I've worked with indexed views/materialized views/DB2's materialized
query tables,
and I think Oracle's implementation is the best.

[Back to original message]