DML on table with the same name as Mview

Question

For some reason, I should keep the Mview which has the same name as a base table. Can you let me know how to issue DML on a base table in this case? As you can see in the below example, I wanted to issue DML for the base table, however, Mview is considered at the first.

DROP TABLE SRC_TABLE PURGE;
DROP TABLE TGT_TABLE PURGE;
DROP MATERIALIZED VIEW TGT_TABLE;
DROP MATERIALIZED VIEW LOG ON SRC_TABLE ;

CREATE TABLE SRC_TABLE(X NUMBER(8) PRIMARY KEY);
CREATE TABLE TGT_TABLE(X NUMBER(8) PRIMARY KEY);

INSERT INTO SRC_TABLE VALUES(55);
COMMIT;


CREATE MATERIALIZED VIEW LOG ON SRC_TABLE WITH PRIMARY KEY, ROWID;


CREATE MATERIALIZED VIEW TGT_TABLE 
ON PREBUILT TABLE WITH REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
AS 
SELECT * FROM SRC_TABLE
/

INSERT INTO SRC_TABLE VALUES (10);
INSERT INTO SRC_TABLE VALUES (20);
COMMIT;


EXEC DBMS_MVIEW.REFRESH('TGT_TABLE');

SELECT * FROM SRC_TABLE;
SELECT * FROM TGT_TABLE;


SQL> DELETE FROM TGT_TABLE;
DELETE FROM TGT_TABLE
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Littlefoot Littlefoot · Accepted Answer · 2020-04-30T06:21:39

TGT_TABLE is a physical table which is used by the materialized view as a "storage"
SRC_TABLE is a table which is used as the "source" of data for that materialized view
you
- can't modify the materialized view or the underlying table which is used as its storage
- can modify table which is used as the source, and that would be the SRC_TABLE, not TGT_TABLE

It is kind of confusing because it looks like you have two objects having the same name, which is impossible. For example:

SQL> select object_name, object_type from user_objects where object_name = 'DEPT';

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
DEPT            TABLE

SQL> create materialized view dept as select * From dept;
create materialized view dept as select * From dept
                                               *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

However, you chose to re-use existing table (TGT_TABLE; it is the ON PREBUILT TABLE clause) so it looks as if there were two objects with the same name. That's how materialized view is designed - has "query" (a "view" which is used to refresh data), and "physical storage" (a "table") which actually contains data.

If you didn't use table that already exists and created a materialized view on some table, you'd still see two objects with the same name. For example:

SQL> select object_name, object_type from user_objects where object_name = 'TEST';

no rows selected

SQL> create materialized view test as select * from dept;

Materialized view created.

SQL> select object_name, object_type from user_objects where object_name = 'TEST';

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
TEST            TABLE
TEST            MATERIALIZED VIEW

See? Something what is impossible to achieve otherwise.

What you did was trying to modify the storage table, and it didn't work:

SQL> update test set loc = 'Zagreb' where deptno = 10;
update test set loc = 'Zagreb' where deptno = 10
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

But, you can / should modify the table materialized view is created against:

SQL> update dept set loc = 'Zagreb' where deptno = 10;

1 row updated.

SQL>

Anyway, modifying the storage table doesn't make much sense as those changes would be overwritten at the next materialized view refresh.

So, in your case, you should update/delete SRC_TABLE, not TRG_TABLE.

DML on table with the same name as Mview

1 Answers