Re: Materialized Views - Can't create

In article <3b4eadd2_at_ns.baltros.ru>, "îÉËÏÌÁÊ says...
>
>Hi, all!
>
> Does anybody use materialized views?
> I (Oracle 8.1.7/Win2000) can't even create materialized view with fast
>refresh on commit. I tried to repeat Oracle's documentation examples, but
>unsuccessfull. Remained max pure, the same result:
>
>create materialized view log on schema.table
> with rowid;
>create materialized view schema.table_mv
>refresh fast on commit as
> select table.rowid
> from table
> ;
>
>==> ORA-12054: cannot set the ON COMMIT refresh attribute for the
>materialized view
>
>What's the matter?
>Thanks
>
>

Materialized views with the REFRESH ON COMMIT attribute were designed to

o transparently maintain aggregates against a single table o transparently materialized joins of multiple tables

a MV that simply copies the values of some columns to another table is not a "sensible" MV -- it adds no value. It is not something that is supported.

See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:424949760671

for an example of a MV -- one that provides additional information in the database (whereas the data in your MV is easily retrieved from the base table itself).

Since you are using ROWID and ROWID in a table is unique, you could turn this into a single table aggregate query:

ops$tkyte_at_ORA817.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> create materialized view log on t with rowid;

Materialized view log created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> create materialized view t_mv   2 build immediate
  3 refresh on commit
  4 as
  5 select t.rowid r, count(*) from t group by t.rowid   6 /

Materialized view created.

But again -- that MV adds *no value whatsoever*. It is only a method to set the "slow=true" parameter in the database.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp