Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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