Re: is there any point to use materialized view without db link?

Mark Bole wrote:
> niy38_at_hotmail.com wrote:
>
> > in that secenario, a view seem more meaningful: without use extra space.
> >
>
> Materialized views play two similar roles in two very different contexts.
>
> In the data warehouse context (without any DB links), they play a role
> in performance improvement, as implied by DA Morgan in his reply ("query
> rewrite"). Your question (or statement, it seems) is that "a view seem
> more meaningful: without use extra space", but with a view, you lose the
> tremendous performance boost due to "pre-querying" your data. Hmm,
> space versus time trade-off ... ;-)
>
> In the Advanced Replication context, MV's were previously known as
> "snapshots" and did indeed rely on DB links -- essentially, the
> incremental changes to a table (or view) in the source database were
> replicated to another database (the replication target) via snapshot
> logs (MV logs) and refresh of the snapshot (MV) at the target. This
> usage is still supported, but is deprecated in favor of Oracle Streams
> replication.
>
> -Mark Bole

We use MVs a lot as a staging mechanism for external data files. Where in the old days we would use SQL*Loader to load a file into a staging table, we now refresh a materialized view that requeries an external table. As well as being simpler and easier to control from PL/SQL, this gives us some free dependency and audit information (which file, where, when). The fact that these MVs are by definition read-only (unless you do something fancy like a partition exchange) could be useful in some situations.

I have also heard of them being used to enforce certain tricky types of constraint:
http://tonyandrews.blogspot.com/2004/10/enforcing-complex-constraints-in.html Received on Fri Oct 14 2005 - 10:15:45 CDT