Hi All,
while digging into materialized views, I hit following:
Logs and refresh based on RowID
CREATE TABLE TEST_TABLE_M(
ID NUMBER(10),
SOME_STRING VARCHAR2(10),
CONSTRAINT TEST_TABLE_M_PK PRIMARY KEY(ID)
);
CREATE MATERIALIZED VIEW LOG ON TEST_TABLE_M
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;
CREATE TABLE TEST_TABLE_S(
ID NUMBER(10),
ID_PARENT NUMBER(10),
SOME_STRING VARCHAR2(10),
CONSTRAINT TEST_TABLE_S_PK PRIMARY KEY(ID),
CONSTRAINT TEST_TABLE_S_TEST_TABLE_M_FK FOREIGN KEY(ID_PARENT) REFERENCES TEST_TABLE_M(ID) ON DELETE CASCADE
);
CREATE MATERIALIZED VIEW LOG ON TEST_TABLE_S
TABLESPACE L_DATA
WITH ROWID, SEQUENCE
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW TEST_TABLE_LOCAL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND WITH ROWID
AS
SELECT S.SOME_STRING S_STRING,
M.RowID Master_RowID,
S.RowID Slave_RowID
FROM TEST_TABLE_M M, TEST_TABLE_S S
WHERE M.ID = S.ID_PARENT;
This works just fine.
Both logs contain just RowID (no primary keys, no join columns). RowIDs of both tables are needed in the defining query of the mview. Refresh of mview seems to be based on RowID despite documentation saying:
Rowid materialized views must be based on a single table and cannot contain any of the following: …, joins, …
The _WITH ROWID_ clause has no effect if there are multiple master tables in the defining query.
Now let's try to define the mview refresh based on PRIMARY KEY without adjusting the logs
DROP MATERIALIZED VIEW TEST_TABLE_LOCAL;
CREATE MATERIALIZED VIEW TEST_TABLE_LOCAL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND WITH PRIMARY KEY
AS
SELECT S.SOME_STRING S_STRING,
M.RowID Master_RowID,
S.RowID Slave_RowID
FROM TEST_TABLE_M M, TEST_TABLE_S S
WHERE M.ID = S.ID_PARENT;
It works still good despite there is no primary key anywhere - not in the logs, not in the defining query of the mview itself. The RowIDs of both master tables are still needed in the defining query.
Strange, isn't it?
Now le't try to rework it all based on PRIMARY KEY only.
DROP MATERIALIZED VIEW LOG ON TEST_TABLE_M;
CREATE MATERIALIZED VIEW LOG ON TEST_TABLE_M
WITH PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES;
DROP MATERIALIZED VIEW LOG ON TEST_TABLE_S;
CREATE MATERIALIZED VIEW LOG ON TEST_TABLE_S
TABLESPACE L_DATA
WITH PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES;
DROP MATERIALIZED VIEW TEST_TABLE_LOCAL;
CREATE MATERIALIZED VIEW TEST_TABLE_LOCAL
BUILD IMMEDIATE
REFRESH FAST ON DEMAND WITH PRIMARY KEY
AS
SELECT M.ID M_ID,
S.ID S_ID,
S.SOME_STRING S_STRING,
M.RowID Master_RowID,
S.RowID Slave_RowID
FROM TEST_TABLE_M M, TEST_TABLE_S S
WHERE M.ID = S.ID_PARENT;
This doesn't work. Why?
ORA-12032: cannot use rowid column from materialized view log on TEST_TABLE_S
It seems that Oracle still expects to have RowID in the mview log of the slave table. Why?
No matter what I try - add/remove both primary keys, both RowIDs to/from defining query, adding ID_PARENT as join column to mview logs of the slave table, it seems that Oracle still wants the RowIDs in the logs.
When I add the RowID to mview logs of the slave table then it asks for RowID in the logs of master table.
So at the end when having defining query containing JOINs it seems that the refresh is always done based on RowID no matter whether I use WITH PRIMARY KEY or WITH ROWID in the definition of the mview.
Can anyone bring more light on this topic?