Re: Materialized view - reading from another view , possible ?

"Manish" <manishrb_at_hotmail.com> wrote in message news:55e6afeb.0401290809.5bf8dccd_at_posting.google.com...
> Hi All,
>
> Is it possible to create a materialized view where certain colums are
> coming from a number of base tables and few from an existing view.
>
> for e.g
> here monthly_sales is a view
>
> create materialized view mv_test
> enable query rewrite
> build immediate
> refresh fast
> start with sysdate
> next sysdate + 1
> as
> select e.deptno, sum(e.sal) sal, sum(v.monthly_sales)
> from emp e, v_monthly_sale v
> where e.emp_id = v.emp_id
> group by deptno;
>
> Also I have read that in order to do fast refresh I need to create a
> log file. How would the create materialized log command look in this
> case ?

There are a lot of limitations to create a fast refreshable materialized view. The answer depends on the oracle version and the definition of the view. Most probably you will not be able to make this a fast refreshable mat view if the view is a complex view.

To create a materialized view log on a table, the command is pretty simple: create materialized view log on <table>;

However, you really need to understand the limitations of creating fast refreshable mat views. Look into the data warehousing guide on tahiti.com. The details are all in there (provided you are not
running an old version).

Anurag Received on Fri Jan 30 2004 - 10:35:06 CST