Re: impossible to delete on materialized views

"Jerome B" <jbaton_at_oxymel.com> wrote in message news:<3cf5f04e$0$229$626a54ce_at_news.free.fr>...
> Hello Group,
>
> I thought materialized views where comparable to tables.
>
> Why is it impossible to delete rows via sql ?
> Is it a way to do it except recreating the views from scratch ?
>
> My views are based on a single table, the options I use for each are
> BUILD IMMEDIATE
> REFRESH FAST ON DEMAND
>
> and before to create them, I set
>
> CREATE MATERIALIZED VIEW LOG ON myTable
> WITH primary key, ROWID
> INCLUDING NEW VALUES;
>
> I use oracle 8i (8.1.7.3 on w2000)
>
> Thanks for your help.
>
>
> Jerome

You need to use UPDATEABLE materialized view (or snapshots), so that changes can be replicated back to their original table.

e.g.
you have a table MASTER_TABLE

and you want a MAT_VIEW

you can use the following procedure:
1.
CREATE MATERIALIZED VIEW LOG ON myTable
WITH primary key, ROWID
INCLUDING NEW VALUES; 2.
CREATE MATERIALIZED VIEW MAT_VIEW
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
FOR UPDATE
AS SELECT * FROM MASTER_TABLE [optionally @DBLINK];

3. check that UPDATABLE SNAPSHOT LOG is created it should be named USLOG$_MAT_VIEW.

For more information see:
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76959/mview.htm#25541 (The replication guide)

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/state14c.htm#2064590 (SQL Reference for CREATE MATERIALIZED VIEW).

For updates to be possible you need to be able to do a fast refresh on the snapshot.

If you need to immediately update the data in the master table, and you don't need a copy of that data you better use a simple VIEW, not materialized view.

Regards and HTH,
Mihail Daskalov,
Brainbench MVP for Oracle Administration, http://www.brainbench.com Received on Thu May 30 2002 - 11:30:21 CDT